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

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
 
Joined
Jun 8, 2016
Messages
1
Reaction score
0
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))
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top