Horizontal lookup that returns value from top row

**kingsHorses** · 04-23-2014, 11:44 AM

I have a table as such:

2000, 2001, 2002
Sean, Billy, Thomas
Jean, Lisa, Amy
Tina, , Grover

I want to use HLOOKUP to search for a name in the table and return the year (which resides in the first row) if a match is found. All the online help I've found for HLOOKUP looks for an item in the first row and returns something from the column below, but this doesn't quite do the trick. INDEX MATCH only seems to work for vertical lookups. Any idea if/how I can accomplish what I'm looking for? Thanks in advance.

**Richard Buttrey** · 04-23-2014, 11:48 AM

Hi,

Just as VLOOKUP requires the value being looked up to be in the first column, HLOOKUP expects it to be in the first row.
Therefore use

Formula:

Please Login or Register  to view this content.

**bebo021999** · 04-23-2014, 11:49 AM

Assuming years in A1:C1
Names in A2:C4
Try this array formula for, i.e "Lisa":

=MAX((A2:C4="Lisa")*A1:C1)

...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer. Press F2 on that cell and try again.