# Lookup Column Header Based On Cell Value

Aug 11, 2009

I have a spreadsheet an excerpt of which is as follows:

K L M N
1 Doors Security Vehicle Key
2 TRUE FALSE FALSE FALSE
3 FALSE TRUE TRUE FALSE
4 FALSE FALSE FALSE FALSE

Want I want to happen is to return the column header where there for a cell which contains "TRUE" in a blank column. If there isn't a cell with the value "TRUE" then to return "N/A". In the above example in blank column P for Row 2 would be "Doors", Row 3 would be "Security,Vehicle" and Row 4 would be "N/A".

In my actual spreadsheet I have 12 columns (L to W) which have the TRUE or FALSE values.

I know it has something to do with either HLOOKUP, INDEX and MATCH but can't seem to get it right. Any ideas?

## Dynamic Lookup Based On Column Header

Jun 21, 2013

I'm trying to calculate a figure in a report tab that reads off the data tab and looks for a specific column based on the header of that column. My explanation is admittedly poor but I've attached a sample file with an outline of what I'm trying to do which I hope makes things clearer.

Sample File.xlsx

## Lookup Column Header Value Based On Matching Table Value

May 16, 2013

I'd like a formula that'll return the column header by matching a lookup value with a table in the second sheet.

eg: sheet 1

Name
Cell
Region

John
111-2222

[Code] .......

The formula should match the name in A2, John, with value from the table in sheet 2 and return the correct region, this case North.

## Lookup Column Header Value When First Cell Is Populated In Row?

Feb 6, 2009

Basically i have a manpower chart with months along the top and names down the far left. People are going to start work in different months so i want to know at the side of each persons name the month they start. Heres a simple example

I have column headers starting at B1 thru F1, with the following values Month1,Month2,Month3,Month4,Month5
In cell D2 i have the value 1
I want Cell A2 to return the value Month3

Whats the formula i need to input in A2

## Lookup Column Header Value When First Cell Is Populated In A Row

Feb 6, 2009

i have a manpower chart with months along the top and names down the far left. People are going to start work in different months so i want to know at the side of each persons name the month they start. Does that make sense? Heres a simple example

I have column headers starting at B1 thru F1, with the following values Month1,Month2,Month3,Month4,Month5
In cell D2 i have the value 1
I want Cell A2 to return the value Month3

Whats the formula i need to input in A2

## Lookup On Column And Row Header

Feb 9, 2010

I need a formula to lookup and retrieve data from a table of values. Given an EMP-ID in column A of the row that contains the formula and given a column name in row 1 of the column that contains the formula, I need to do a lookup. So, in effect, I am doing a lookup based on two values .. the column header and the row header.

I have attached a small model to illustrate what I need. Cell C-3 in the first worksheet will contain the formula to lookup and retrieve the value in cell C-7 in the second worksheet, based on the values in cell C-1 (column header = “Database Col 2”) and in cell A-3 (row header = “257”) in the first worksheet.

## VBA - Lookup For Value In A Range And Returns Its Column Header

Jan 16, 2014

I have a table (A1:C5)

A2:C5 contain the data
A6 contains the value I want to search for I use this formula to return the column header of A6 A6=INDEX(A1:C1,SUMPRODUCT((A2:C5=A6)*COLUMN(A2:C5))) Now, if there are duplicate values in A2:C5, this will return more than one column header What I need is a vba code to view a msgbox with all column headers returned in this case

Nov 18, 2008

I have a list of names and the chores they need to do on a certain day. I need a formula that returns the chore the person needs to do when the date is filled in.

Ex: Sheet2 Col:A has names filled in A2:11 , row B1:J1 has dates that you would input.
formula goes in B2:J11
Sheet1 is the master with all the data the formula would be pulling from.
column A2:A11 has the names , row B1:S1 has the chore that needs to be done.
B2:S11 has the dates already filled in.

## Return Column Header Based On Column Criteria And Number Value

Feb 7, 2014

I have the following sample data set and I'm trying to return the appropriate column header based on criteria (i.e. DDD) and a number value which will be somewhere within the range of the table. In example below, the value returned should be Header 2 because the value is greater than those in Header 1 column (range H9 to L26).

Here's data table:

ZZZ5.0015.0050.00130.00
ZZ5.0015.0050.00130.00
Z5.0015.0050.00130.00
YYY5.0015.0050.00130.00
YY5.0015.0050.00130.00
Y5.0015.0050.00130.00
DDD5.0015.0050.00130.00
DD5.0015.0025.0075.00
D5.0015.0020.0065.00
RRR5.0015.0015.0045.00
RR2.5010.0010.0030.00
R1.503.0010.0025.00
UUU0.751.505.0020.00
UU0.751.505.0020.00
U0.751.505.0020.00
P0.751.505.0020.00
T0.100.105.0018.00

CriteriaNumberValue

## How To Lookup Row # Based On Content Of Another Column's Cell

Oct 24, 2005

I have a 3 column table with 20 rows. The first two columns contain travel
expense data. The cells in the third column are blank exept for whichever
cell I enter an "x" in. Please let me what formula or worksheet function do
I use to calculate the travel expenses based on the data in the first two
columns that correspond to the row of the cell with an "x" in the third
column?

## Return Value From Column Based On Header Value?

Jul 16, 2013

Cells A3:A500 contain product numbers, e.g. 1001, 1002, 1003 etc.. etc...
Cells G2:N2 are column headers, e.g. North2011, South2011, North2012, South2012 etc... etc...
Cell F2 is a lookup that will return a year, e.g. 2011, 2012, 2013 etc... etc...

Against each product is a "yes" if it was sold in the north or south region for each year. This could be both north and south, one or the other or none at all.

What I would like is a formula in cells E3:E500 that looks at the year in cell F2 and if there is a "yes" in either the North or South for that year to return "yes" otherwise blank.

I am unable to upload a sample document due to firewalls.

## Return Column Header Based On Row Value

Mar 11, 2014

Requirement
Vendors
Evaluation Results

Sr.
ITEM DESCRIPTION
BRAND
UNIT
A-Z
AL AMEEN
COLOR WORKS
INDEX
OXFORD
PENCIL
SUPPLIES STORE
WAVES
LOWEST PRICE
SECOND LOWEST
Vendor Lowest Price
Vendor 2nd Lowest Price

[Code] .......

I have above table and wants to get the formula for getting the 1) Vendor Name of the Lowest Price per item 2) Vendor Name of the 2nd Lowest Price per item 3) and be able to copy down the formula for other items.

## Delete Column Based On Column Header

Apr 29, 2009

I have a spreadsheet with several columns (all column names are in row 1).

I have 2 of column names. Note that their actual column# might change so the macro would have to look for the column name and NOT the column position ...

## Looking Up Column Header Value Based On Color And Position In Row

Dec 12, 2009

I am trying to create a schedule using colors. The colors will indicate the start time, break time, lunch time, break time (second break) and end time. see attached document.

Thank you for attempting to solve this puzzle using formulas and or VBA code. I am trying to automate the schedule so the numerical times automatically appear when I "paint" the schedule.

## Selectively Copying Cells Based On Column Header?

Jul 9, 2013

I'm trying to copy cells from a column in one workbook to cells in a column in another workbook based on what's in a header column.

Such as

Original workbook
Mat Qty
A 1
C 3
D 2
E 4
G 5
Total 15

Target workbook becomes

Mat Qty -> Mat Qty
A -> A 1
B -> B
C -> C 3
D -> D 2
E -> E 4
F -> F
G -> G 5

My code so far (which might not be worth reading) is:

Code:
Sub CopyColumnToWorkbook()
Dim sourceColumn As Range, targetColumn As Range, sourceColumnValue As Range, targetColumnValue As Range
Dim mySourceCell As Range, myTargetCell As Range

[code].....

I'm getting a runtime error ('1004'. Application-defined or object-defined error) on the Copy statement within the If sttement.

## Macro To Insert Formula Based On Column Header

Feb 24, 2009

I have to use several dozen pivot tables a day. I already use a couple of macros to do a lot of the tedious formatting, but if I could automate this it would save me a lot more time. I have been trying to both write a code and modify code written by the recorder but have not even come close to anything that works. What I want is to have a macro that searches through the column header for predefined names and if it comes across one of these names it will insert a specific formula, based on its name, in the first cell below the header. Then it will autofill to the last row and move on to searching for the next predefined header name. If that name is not found, it will move to the next predefined header name. One major problem is that the formula to insert under any specific header name is based on other columns in the table that aren't always in the same range. So, not only do I need it to search for columns to place a formula in, that formula has to search for the appropriate column to find the correct value to use in the calculation.

For example, one predefined column name would be Avg Price. The formula for that column would be (Sales/Qty Sold), but these 2 columns could be located anwhere in the table. How can I get it, once it has found a column that needs a formula, to locate the correct column/cell to get the correct value from the sales and Qty Sold columns?

## Delete Rows Based On Criteria While Referencing Column Header

Jun 6, 2007

I'm trying to use VBA code to delete certain rows based on a couple of criteria as follows:

1) If column header is "TERMINAL NAME" and the cell value in that column is "BONDDESK", I need the entire row deleted.

2) Similiarly, if the column header is "PRODUCT TYPE" and the cell value in that column is "CORP", that entire row needs deleted as well.

The information is coming from another source, so the columns may change from day to day, i.e, "TERMINAL NAME" may be in Column L one day but in Column O another day.....

## Convert Numbers To Postitive/Negative Based On Column Header

Mar 7, 2008

I have this data source file that comes from a back office program, and basically what i want it to do is to turn the number negative in column C when the header of that column in column A is WITHDR and leave it positive when it is CONTR. What i was thinking was that it could go to column A from column C and go up each cell until the cell is not a date and multiply the column C number by -1 depending the column A header equals WITHDR and do that for every cell in column C, but I dont know how to proceed, im new at VBA.

## Rearrange (sort) Columns Based On Number In Column Header String

Apr 3, 2014

I want to rearrange(sort asscending) columns based on numerical value in column header string through VBA macro. Please check attachment.

i.e. (Present Data)
# A B C D
1 col.1 col.4 col.3 col.2

(Output Data )
# A B C D
1 col.1 col.2 col.3 col.4

test.bmpâ€Ž

## VBA Loop Through Worksheet And Format Each Column As Text, Or Date Based On Header

Jun 30, 2006

I am trying to figure out a way to condense my code (improve my code) Right now I am using a Macro in Excel to go through a workbook by selecting each column individually and if there is something in the header then formatting that column as text. The problem with the way I am currently doing it, is that due to size limitations on the VBA code, I can only select columns A to Z, and if there is more data in the worksheet it is not formatted. Also I would like the code to check the header and if the word date is in there, format it as a date instead. Also if there is a way to delete any invalid ranges in the workbook

Sub Format()
Dim VarFileName As String
Dim VarPath As String
Dim VarSavein As String
Dim wsheet As Worksheet
VarSavein = Sheets("sheet1").Range("C2").Value
VarFileName = Sheets("sheet1").Range("A2").Value
VarPath = Sheets("sheet1").Range("B2").Value
Workbooks.Open VarPath & VarFileName
For Each wsheet In ActiveWorkbook.Worksheets
Sheets(wsheet.Name).Select
Columns("A:A").Select
Selection.TextToColumns Destination:=Range("A1"), DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
:= Array(1, 2), TrailingMinusNumbers:=True..........................................

## VBA Code To Copy And Paste To Specific Rows Based On Matching Column Header?

Dec 7, 2011

I have a spreadsheet and I was wondering if there is a way to copy from a specific range in say A1:A5 in Sheet1 and paste into Sheet2 where the column matches cell A1 in Sheet1.

I am trying to do this in VBA and I was wondering if there is a way to do this.

## Copy Column Data From Multiple Worksheets To Single Worksheet Based On Header Criteria

Oct 18, 2013

I have a workbook with many sheets of similar but not identical data. I need to extract columns from each sheet based on 5 header criteria and paste them to a single sheet. Each worksheet contains these 5 criteria.

I've been working with the VBA script I've pasted below. It's grabbing the 5 column criteria that I have in sheet 12, and comparing them to sheet 1 in the workbook, then copying them to sheet 12. This much is good, but I need the script to also return the data from the other worksheets as well. I've tried modifying the script based on other loop functions in other scripts I've found, but I'm not having any luck.

## Dynamic Range Based On Cell Between A Header And Footer Cell

Sep 2, 2008

I need to create a dynamic range based on cell between a header and footer cell. The header cell ( A8 ) will remain static, however, the footer cell starts at A10 and then will move down as rows are added.

## Right Most Cell With A Value And Return Column Header?

Dec 11, 2012

My file is set up like this:

Deal Jan Feb Mar....... Dec
A 0 11 0
B 0 0 23
C 13 0 12
and so on

Meaning deal A got cash of 11 in Feb and B of 23 in March

For a certain month (which could change), I want to specify the most recent month of a payment and return the month

So in April, row A returns 2, Row B March and Row C March

## Populating A Cell With A Column Header

May 13, 2013

I have a spreadsheet with a number of columns from a database export, where values in the column are either "TRUE" or "FALSE," Depending on whether or not the box was checked in the database. I am wondering if there is a way to populate a new cell with the text from the column if the value is "TRUE."

For example:

BOY
GIRL
John Doe
TRUE
FALSE

Is there a way that I could have another cell populated (I guess via function or macro) with "BOY," since the option for boy is true?

## Return ALL Non-zero Cell VALUE And Corresponding Column Header For All IDs

Oct 31, 2013

I have a table like the following where each ID can have no, 1 or multiple values associated with it:

Table1
ID>>>A>>>B>>>C>>>D
1>>>> >>> >>>10>>>
2>>>> >>> >>> >>>>50
3>>>5>>> >>> >>>>2
4>>> >>> >>> >>>>

I have another table that only lists IDs
Table2
ID
1
2
3
5

I want a VBA code such that if ID is in Table2, VBA looks for the matching ID in Table1 and if the matching ID found, it looks for ALL non-empty cells and if a non-empty cell is found in the row, it returns the value in the cell and the header to give something like this

Table3
ID>Column1>Value1>Column2>Value2
1>>>>c>>>10>>
2>>>>D>>>50>>
3>>>>A>>>5>>>>>>D>>>>>>>2>>>

## Index / Match Return Header Value Based On Row And Cell Value

Apr 24, 2012

I have a rather large table that I need to return the column heading from.

Within the table I have a list of Names in the left most Column (A) and a list of Percentiles in the header row (row 2) (incrementing by 5%). By inputting the name and a cell value I am trying to return the column header.

Using an example;

5% 10% 15%
Bob 3.5 4.1 4.15
John 3.6 4.2 4.30
Chris 3.3 4.9 4.95
Sammy 3.7 4.6 4.7

Like I said, knowing the name and the cell value, I want to return the percentage value. So for example, Bob would be one input value, and another input value would be 4.1. I would want excel to return 10%.

I have found different suggestions using a combination of index/match but even using these functions I cannot get it to return the COLUMN value instead of a cell value.

## Find First Visible Cell Under Header Of Column A?

Jul 1, 2014

ive added a filter, and on column A i want to find the first blank row under the header and type the word 'tech' and fill down to the last row populated against column b.

## Change Cell Depending On Column Header

Jul 11, 2007

I have a spreadsheet with names and start / finish dates columns down the left hand side then a row of week commencing dates along the top.

for each name row I would like to change teh fill colour of a cell to green to represent the week in which they started and to red for the week in which they finished. I therefore need to cross reference the start and finish dates for each name with the relevant week commencing dates at the top. Somehow! I presume there is some kind of vlookup type formula that I need to use in conditional formatting, but I am not sure what.

Nov 18, 2011

I've been trying find an appropriate formula to extract the column header from a table in a different sheet if the row header and value in that table is known.

in the lookup table the row titles (column A) are product codes, column titles (row 2, D through AX) are business names and the table values are quantity.

In a different table I have product codes in column A and in column B i have the max number/quantity of products for that code. In column C i want to put the company name associated with the product and the number/quantity.