Look up column header

[jonnyd]

Hi, I'm sure this is quite simple and I'm just not understanding the various lookup functions but here's my problem:

I have a number of columns with headers, and a tax rate in the cell below the header. I need to create a formula that will return the tax rate depending on the which column has an entry so e.g.

row 1 has column headers for 5 columns
row 2 has tax percentages for the 5 columns
row 3 has an entry of xxx in column 3

I need to put a formula in another cell that will look up which column on row 3 has an entry, and will then return the percentage for that column from row 2.

As I said I'm sure hlookup or something will do this, I just can't seem to get the **** thing working...

Any help would be much appreciated...

 

[Aladin Akyurek]

Do you have just one 'xxx' in row 3?

 

[jonnyd]

yes, only ever one entry in a row.

 

[Aladin Akyurek]

yes, only ever one entry in a row.

=LOOKUP(REPT("z",255),$A$3:$E$3,$A$2:$E$2)

 

[jonnyd]

Hi again, thanks for the response, but I've got a bit of a problem with it. The values in the rows will be now numbers and not constant (e.g. one row could have 100, another could have 59, rather than having xxx in them). Using the formula supplied above works if the entries were as I said (xxx) but they're not - sorry!... any other ideas?

 

[Aladin Akyurek]

Hi again, thanks for the response, but I've got a bit of a problem with it. The values in the rows will be now numbers and not constant (e.g. one row could have 100, another could have 59, rather than having xxx in them). Using the formula supplied above works if the entries were as I said (xxx) but they're not - sorry!... any other ideas?

=LOOKUP(9.99999999999999E+307,$A$3:$E$3,$A$2:$E$2)

if there is just one number per record. BTW, the formula picks up the header corresponding to the last numeric entry.

 

[scant]

what about more than one?

Hello,

i have this same problem where i have several values in the lookup array row, only i need to it to lookup the first value header though excel only wants to look up the last valued header by default



does anybody know a solution?

thank you if you do

 

[Aladin Akyurek]

Re: what about more than one?

Hello,

i have this same problem where i have several values in the lookup array row, only i need to it to lookup the first value header though excel only wants to look up the last valued header by default



does anybody know a solution?

thank you if you do

Do you want to retrieve the header corresponding to the first numeric value like in:

DAX, VAX, BIX, TDX
"", 3, 7, 1

where 3 is the first value with as result: VAX?

 

[scant]

yes that is correct, specifically it is date headings and i want to lookup the startdate for the column with the first date value in each row, so like your example only

01/05,02/05,03/05,04/05
"","",x,x
"","","",x
x,x,x,""

which is exactly the same as what you say essentially, except i have noticed that dates behave in peculiar ways sometimes...

thank you for your help Aladin

 

[Aladin Akyurek]

yes that is correct, specifically it is date headings and i want to lookup the startdate for the column with the first date value in each row, so like your example only

01/05,02/05,03/05,04/05
"","",x,x
"","","",x
x,x,x,""

which is exactly the same as what you say essentially, except i have noticed that dates behave in peculiar ways sometimes...

thank you for your help Aladin

Let A1:A4 house the date headers.

=INDEX($A$1:$A4,MATCH(TRUE,B1:B4<>"",0))

which you need to confirm with control+shift+enter instead of just with enter.