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!


--
ayl322
------------------------------------------------------------------------
ayl322's Profile: http://www.excelforum.com/member.php...fo&userid=9846
View this thread: http://www.excelforum.com/showthread...hreadid=570867

HLOOKUP may work?

"ayl322" wrote:


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!


--
ayl322
------------------------------------------------------------------------
ayl322's Profile: http://www.excelforum.com/member.php...fo&userid=9846
View this thread: http://www.excelforum.com/showthread...hreadid=570867

Do a help search on HLOOKUP.


--
pikapika13
------------------------------------------------------------------------
pikapika13's Profile: http://www.excelforum.com/member.php...o&userid=10892
View this thread: http://www.excelforum.com/showthread...hreadid=570867

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


"ayl322" wrote in message
...

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!


--
ayl322
------------------------------------------------------------------------
ayl322's Profile: http://www.excelforum.com/member.php...fo&userid=9846
View this thread: http://www.excelforum.com/showthread...hreadid=570867

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(MAT CH(A1,C3:C99))),c2,0)
)

Etc.


pikapika13 wrote:
Do a help search on HLOOKUP.


--
pikapika13
------------------------------------------------------------------------
pikapika13's Profile: http://www.excelforum.com/member.php...o&userid=10892
View this thread: http://www.excelforum.com/showthread...hreadid=570867

but HLOOKUP would search for the matching data in the column headings
only, right?
i need it to search the body of the table for the data and return the
column heading?


--
ayl322
------------------------------------------------------------------------
ayl322's Profile: http://www.excelforum.com/member.php...fo&userid=9846
View this thread: http://www.excelforum.com/showthread...hreadid=570867

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 guess
you learn something new everyday! :)


--
ayl322
------------------------------------------------------------------------
ayl322's Profile: http://www.excelforum.com/member.php...fo&userid=9846
View this thread: http://www.excelforum.com/showthread...hreadid=570867

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