Look up column header

jonnyd

New Member
Joined
Feb 18, 2005
Messages
5
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...
 

Some videos you may like

Excel Facts

Pivot Table Drill Down
Double-click any number in a pivot table to create a new report showing all detail rows that make up that number

jonnyd

New Member
Joined
Feb 18, 2005
Messages
5

ADVERTISEMENT

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

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,201
jonnyd said:
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

New Member
Joined
Aug 9, 2004
Messages
19

ADVERTISEMENT

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

:rolleyes:

does anybody know a solution?

thank you if you do
 

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,201
Re: what about more than one?

scant said:
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

:rolleyes:

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

New Member
Joined
Aug 9, 2004
Messages
19
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

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,201
scant said:
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,123,323
Messages
5,600,954
Members
414,417
Latest member
Nobu

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top