Microsoft Excel Functions: HLOOKUP

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

Download the accompanying file(s) for this article.

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.

HLOOKUP Syntax

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

HLOOKUP

We want to create a HLOOKUP formula that will retrieve the value for each field dependent upon the order ID

HLOOKUP

Let’s start with the ‘Units’ field. Our HLOOKUP formula looks like this:

=HLOOKUP($K$2,$B$2:$G$7,2,0)

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

 

If you’ve found this article interesting and or useful please share it on social media using the handy share buttons

Subscribe to our mailing list to be the first to know when new articles are published

At Happy Duck we build bespoke business and financial management tools for small, medium and large companies that help them achieve their goals. This makes us happy. Find out more about us and about our services.

More in Formula

0 Comments

Leave a comment

Your e-mail address will not be published. Required fields are marked *