opposite of vlookup function?

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

[pikapika13]

Do a help search on HLOOKUP.

[Dave F]

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

[[email protected]]

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.


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

[ayl322]

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?

[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


"ayl322" <[email protected]> wrote in message
news:[email protected]...
>
> 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
>

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

[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