HLOOKUP is the lesser used cousin of VLOOKUP, but can be just as useful. Learning how to use HLOOKUP can help in situations where VLOOKUP will not work
HLOOKUP operates in practically the same way to VLOOKUP apart from one important point. It is a horizontal lookup – that’s where the ‘H’ comes from.
If you are familiar with VLOOKUP the syntax for HLOOKUP is very easy to understand:
=HLOOKUP(value, table, index number, match type)
What does that mean?
=HLOOKUP is the function we are using
(value, is the value we are looking for in the..
table, which is where the data is stored
index number, is the position (row) in the table that our data stored on,
match type) do we want an exact (0) or approximate (1) match
In simple terms, the value you are looking for should be stored in the table headers i.e the first row of the data table. The data you want to retrieve will be stored in a row under the header, however, you need to know which row.
HLOOKUP in Action
In the accompanying workbook, you will find a data table holding order information
We want to create a HLOOKUP formula that will retrieve the value for each field dependent upon the order ID
Let’s start with the ‘Units’ field. Our HLOOKUP formula looks like this:
Breaking the Formula Down
=HLOOKUP($K$2, We want to lookup the value in cell K2. This cell is made absolute as it needs to remain constant when the formula is dragged down to the other cells
$B$2:$G$7, This is our data table, or the range where our data lies
,2 Here we are telling Excel which row our data for the ‘Unit’s field is stored on, which is row 2 of the data table
0) We want an exact match
When the formula has been written drag it down to the other cells. Try changing the Order ID from 10001 to 10002 and watch what happens.
As with most Excel formulas, HLOOKUP can be improved upon by using a little forward thinking. A future article will follow detailing how to improve upon the standard formula.
In the meantime, leave your comments below detailing how you would improve HLOOKUP to be much easier to work with