Lookup & Display Value in Topmost Row

Hi all,

I'm trying to do a Lookup function but can't really able to work out. Appreciate if anyone could help me out.
Thanks in advance!

My data is made up of : Column A is Animal Category (eg. Cat, Dog, Horse, Deer) & Column B is Insect Category (Moth, Beetle, Mosquito, Ladybug).

In column C, I would have the subject (eg. Dog / Beetle).
I want to do a lookup formula in D1 so that whenever I put in the subject in column C, it would identify what category it belongs to by 'searching' the data in columns A & B. For example, by entering "Dog" in C1, D1 will automatically search for "Dog" in data range A:B & display "Animal".

HI donkeybusiness,

try using below formula:-

Formula:

Please Login or Register  to view this content.

Regards,
DILIPandey
<click on below * if this helps>

This formula might work better with a lot of catagories (assuming that the value will be unique to a specific catagory

=INDEX($A$1:$B$1,SUMPRODUCT(($A$2:$B$5=C2)*COLUMN($A$2:$B$5)))
A1:B1 are your catagory headers, A2:B5 is your data range

isnt that an array formula? this one certainly is

=INDEX(A1:G1,MAX((A1:G6=J2)*COLUMN(A1:G6)-MIN(COLUMN(A1:G6))+1)) where j2 has say tiger in it

The one I suggested is not an array function.

hmm it returns value when i tried it and had to array enter it to work
now that is strange it works in one workbook but not another
it seems to not work in one i'd opened from a forum link but ok in one i open on my pc

That is odd. Computers are funny things.

=IF(SUMPRODUCT(--($A$2:$G$7=J2))=0,"Not Available",INDEX($A$1:$G$1,SUMPRODUCT(($A$2:$G$7=J2)*COLUMN($A$2:$G$7)))) where the item to be looked up in is J2
Good?