HLookup - The overlooked cousin

Today I want to look at =Vlookup’s often overlooked cousin =HLookup (Vertical lookup to Horizontal Lookup).

They both accomplish a similar task. =Vlookup allows you to search for a value in a column and return a value in a column to the right of where your search value is stored.

=Hlookup allows you to search for a value in a top row of a table and return a value in a row below of where your search value is stored. 

You would use =Hlookup when your value is located in a row across the top of a table of data, and you want to look down a specified number of rows. You would use =VLOOKUP when your value is located in a column to the left of the data you want to find.

Confused? Don’t be.

Both have a similar formula structure.

Hlookup(Lookup Value, Table array, Row index num, Range Lookup)

Vlookup(Lookup Value, Table array, Col index num, Range Lookup)

Lookup Value - The value you want to find. If its text place a " around the text.

Table Array - The columns and rows where you want to search for your value and return value.

Col  / Row Index Num - The column or row of the value you want to return.

Range lookup - True or False, not required but specifies whether you want your lookup to find an exact match or an approximate match. If TRUE or omitted, an approximate match is returned. In other words, if an exact match is not found, the next largest value that is less than lookup_value is returned. If FALSE, HLOOKUP will find an exact match. If one is not found, the error value #N/A is returned.

Here is some sample data.

	A	B	C	D	E	F	G	H
1	AA	BB	CC	DD	EE	FF	GG	HH
2	1	2	3	4	5	6	7	8
3	9	10	11	12	13	14	15	16

Here I have columns A through H with data in rows 2 and 3. My header row has duplicated letters (AA, BB, CC. etc.)

Let’s use =HLookup to bring back the value of 5 in column E.

=HLOOKUP("EE",1:2,2,FALSE)

So what does this formula do???

It asks Excel to find the value EE in the designated row array (rows 1 & 2), then return the value in row 2.

If I wanted to return the value in row 3 (13) in column E, my formula would be…

=HLOOKUP("EE",1:3,3,FALSE)

Now it is important to point out that the Array 1:3 indicates the rows I select for my formula. If my header row starts in row 5 and my data is in rows 6-7, then my formula would change.

=HLOOKUP("EE",1:2,3,FALSE) to =HLOOKUP("EE",5:7,3,FALSE)

Of course I can reference a cell instead of a hard coded value such as “EE”. In this example I want to look for the value in cell G18 and return the value un row 3 of my array.

=HLOOKUP(G18,1:3,3,FALSE) to =HLOOKUP("EE",5:7,3,FALSE)

Wildcards

If range_lookup is FALSE and lookup_value is text, you can use the wildcard characters - (?) and asterisk (*).

A question mark matches any single character (as shown in cell F8 in my example).

 If you have 6 question marks, then you are trying to return a field with 6 characters in length.

An asterisk matches any sequence of characters.

 If you want to find an actual question mark or asterisk, type a tilde (~) preceding the character

Now I almost never use =HLookup in my daily Excel use, but do rely heavily on =VLookup. How do you use =HLookup in your daily work?