opposite of vlookup function?

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!
 
B

Bernie Deitrick

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
 
C

chris.cudmore

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.
 
A

ayl322

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
 
A

ayl322

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! :
 
B

Bernie Deitrick

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
 
Top