Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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? |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
lookup using row & column heading. | Excel Discussion (Misc queries) | |||
Lookup value,return column heading | Excel Worksheet Functions | |||
Lookup Value and Return Column Heading | Excel Worksheet Functions | |||
Return Column Heading after lookup | Excel Worksheet Functions | |||
Lookup min & column heading | Excel Worksheet Functions |