opposite of vlookup function?

  • Thread starter Thread starter ayl322
  • Start date Start date
A

ayl322

i need a formula that is kind of opposite of vlookup.

in a table of data, i need to locate a specific data and return the
column heading of that data.
for example, let's say i have a table:
fruit veggies dairy
apple cucumber cheese
banana squash milk

if a1 = "apple"
i need to find a1 in the table and return "fruit"

is this possible?

any help would be appreciated!
 
You need an extra column of helper cells.

With your example table in A1:C3, enter

D1 Apple
D2 =IF(ISERROR(MATCH($D$1,A2:C2,FALSE)),0,MATCH($D$1,A2:C2,FALSE))
copy D2 to D3

Then use

=INDEX(1:1,MAX(D2:D3))

to return 'Fruit' - but note that, botanically, both cucumbers and aquash are fruit ;-)


HTH,
Bernie
MS Excel MVP
 
Hlookup doesn't do what he wants.

He wants to search the table data for a particular value, and then
return the header.

For a single column:

=IF(NOT(ISNA(MATCH(A1,B3:B99))),B2,0)

where A1 is the test value, B3:B99 is the lookup column and B2 is the
header.

Now you just have to replace the 0 in the false portion with similar
lookups for the other columns in your table.

=IF(NOT(ISNA(MATCH(A1,B3:B99))),B2,IF(NOT(ISNA(MATCH(A1,C3:C99))),c2,0)
)

Etc.
 
but HLOOKUP would search for the matching data in the column heading
only, right?
i need it to search the body of the table for the data and return th
column heading
 
thank you~
i think that will work.

lol..are cucumbers and squash really fruit?
i was just using that as an example so it doesn't matter, but i gues
you learn something new everyday! :
 
lol..are cucumbers and squash really fruit?
i was just using that as an example so it doesn't matter, but i guess
you learn something new everyday! :)
Click to expand...

Botanically, if not practically. Scientifically, a fruit is a body that contains seeds, usually (if
not always) produced where a flower had been. Tomatoes, beans, peas, watermelon, peppers, eggplant,
etc. are all fruit. Vegetables are things where you use the leaves, stems, or roots. Carrots,
swisschard, celery, beets, spinach, rhubarb, etc. are vegetables.

The practical definition is more about use - if they are used as dessert, then they are fruits - or
something along those lines....

Bernie
MS Excel MVP
 
This is great info, I have the same issue. If I could continue on this same thread, the question is simple (I hope): If I need the entire master table to be searched in order to populate that one field with one of the headers, can I do that with one formula? How does that change the provided formula below? Given that excel has multiple ways of doing the same thing, could there be an easier way for very large sets of data?
=IF(NOT(ISNA(MATCH(A1,B3:B99))),B2,IF(NOT(ISNA(MATCH(A1,C3:C99))),c2,0))
 
Back
Top