how to determine column heading from lookup
 

Say I have a table for phone commission

---------------------------
Handset - TariffA - TariffB
---------------------------
Nokia1 - £50 - £100
Nokia2 - £60 - £200
Sony1 - £60 - £300
---------------------------

and on another sheet I have

---------------------------
Handset - Expected - Actual
---------------------------
Sony1 - £300 - x
---------------------------

I know I have a Sony1, and that I have £300 - how do I return
"TariffB" as the cell value, X?

Any ideas on how to do this?

how to determine column heading from lookup
 

=if(B2=vlookup(A2,Table,2,false),"TariffA","Tariff B")

--
Regards,
Tom Ogilvy


"Paul" wrote in message
oups.com...
Say I have a table for phone commission

---------------------------
Handset - TariffA - TariffB
---------------------------
Nokia1 - £50 - £100
Nokia2 - £60 - £200
Sony1 - £60 - £300
---------------------------

and on another sheet I have

---------------------------
Handset - Expected - Actual
---------------------------
Sony1 - £300 - x
---------------------------

I know I have a Sony1, and that I have £300 - how do I return
"TariffB" as the cell value, X?

Any ideas on how to do this?

how to determine column heading from lookup
 

Thanks Tom,

sorry, but that's not what I'm looking for, I should have explained
more clearly.

The table above will go from TariffA to Tariff X, and the same for the
phone models, say around 40

Maybe a macro would be better suited to my needs?

Get the phone model and money in
Search the table for the model
Use the row number to look for the money in
return the column number and use that to find the column title

how to determine column heading from lookup
 

Phones listed in A2:A41
Tariff names in B1:Y1
Tarriff amounts in the body of the table. B2:Y41
A1 is the upper left corner of the table above the phone list and to the
left of the tariff list.
In AB2 I enter the name of the phone of interest and in AC2 I enter the
money amount. This formula returns the Tariff name.

=OFFSET(A1,0,MATCH(AC2,OFFSET($A$1,MATCH(AB2,$A$2: $A$41,0),1,1,23),0))

You should be able to adapt this approach to address your specific
situation.

--
Regards,
Tom Ogilvy


"Paul" wrote in message
oups.com...
Thanks Tom,

sorry, but that's not what I'm looking for, I should have explained
more clearly.

The table above will go from TariffA to Tariff X, and the same for the
phone models, say around 40

Maybe a macro would be better suited to my needs?

Get the phone model and money in
Search the table for the model
Use the row number to look for the money in
return the column number and use that to find the column title

how to determine column heading from lookup
 

Cheers Tom,

that's awesome stuff - working a treat! Gonna sit with the help files
and see if I can figure out how it works!