The VLOOKUP and HLOOKUP formula in Excel allow you look up a value in one column or row and return a corresponding value from a different column or row. VLOOKUP is a vertical lookup and HLOOKUP is a horizontal lookup

The table of data below contains 4 columns.  A Product Number, the Supplier, the quantity in stock and the cost price.  With a small table of data like this it is easy with the eye to look at a product number and see who the supplier is, how much is in stock and how much it cost.  But imagine you had a large table of data, scrolling through it, or using the filters to find the sock code is not efficient.  This is where VLOOKUPs are very powerful.

VLOOKUP Syntax

The syntax for VLOOKUP is:

VLOOKUP = (lookup value, table array, column index, range lookup)

Lookup value is the value you wish to look up.  This value must be in the far most left column of the table

Table array is the table in which you want to search

Column index is the column number you wish to return the data from

Range lookup offers a true or false selection.  Where true is an exact match and will only return a value where an exact match is found. When using an Approx. match (false), items in the table must be sorted from lowest to highest, or in alphabetical order.

Constructing a VLOOKUP formula

Using the above table of data, given a Product Code lets apply a VLOOKUP to return the Supplier and the Cost price

We begin our formula with

=VLOOKUP(

The first criteria VLOOKUP wants is the lookup value.  In this case we wish to look up the Product code AA01.  We could hard code this value into our lookup and as it is in text we would need to include it in “”

=VLOOKUP(“AA01”,

Or we could reference the cell that contains this value.  In this case we have the value in cell F2

=VLOOKUP(F2,

These criteria is then separated from the next criteria (Table Array) with a comma.  We then select the entire table in which we wish to preform the VLOOKUP.

=VLOOKUP(F2,A1:D7

The entire table is the table_array

Remember the far most left column of the table must contain the lookup criteria.  If we were looking up a Supplier we would not select column A, but instead start with column B.  Therefore, if we wanted to lookup the supplier the table array would be B1:D7.

The table array and the Column index are separated with a comma.  The column index is the column number you wish to return the value from. Looking at our table, the Produce Code column, column A, is column index 1, supplier is 2, qty in stock is 3 and cost price is 4.  As we wish the supplier to be returned, we would select 2 as our column index. 

=VLOOKUP(F2,A1:D7,2,

If we were looking up the Supplier to return the cost price, the first column in the selected table would be B, and this would be column index 1, qty in stock would be column index 2 and Cost price would be column index 3.

The final part of the puzzle is the Range Lookup. We must select an exact match or an approximate match.  An exact match will look up the exact lookup value that you stated.  An approximate match will lookup the closets match.  In this case we want an exact match.  This is denoted as a FALSE

=VLOOKUP(F2,A1:D7,2, FALSE)

Exact or Approximate Match in VLOOKUP

If we change our product code to BB04 we will be returned with an #NA error as Excel is unable to find this value in the lookup table.

By editing our formula and changing the Range Lookup to TRUE, (which is an approximate match) Excel will look down the table and find the value higher than the lookup value. It will then go back 1 cell and return the lookup value for that cell.

For example, if your lookup value was 6 and the table contained 1,2,3,4,5,7.  Excel would go down the table and see that there is no exact match for 6. However it will find a value above 6, in this case being 7. Excel would then step back 1 and use 5 as the lookup value.  For this to work, the column of data must be sorted lowest to highest or alphabetically from A-Z.

Preparing a VLOOKUP to return the cost price using the product code as the lookup value follows the same steps as above. However there is 1 major different in the formula.  The cost price can be found in the 4^th column of the table of data selected as the table array.  Therefore the column index number is 4.

HLOOKUP

The syntax for HLOOKUP is

HLOOKUP = (lookup value, table array, row index, range lookup)

The syntax is the relatively the same as VLOOKUP.  However instead of column index you have row index.

Although VLOOKUP and HLOOKUP are powerful functions that will quickly search through data, they can be even more powerful when combined with other functions and formula.  However there are some limitations.  Limitations include Vlookup will only look to the right, HLookup will only look down.  These functions are also not dynamic so if the data is amended then the functions may return incorrect results.

The good news is that there are ways to overcome these limitations of both VLOOKUP and HLOOKUP

Practice Activity (Solution in video below)

Earn STEEM tokens which you can use against the price of our premium training by carrying out this activity and posting your solutions, images, comments or feedback on the activity or article in the comments section of this blog post.

Copy the table of data below and carry out a VLOOKUP for the Invoice number to return both the customer and the invoice value.

In the comments below answer the following questions

What formula did you use to return the customer?

What formula did you use to return the Invoice value?

What would happen your formula if you entered a new column of data between the invoice number and the Customer in the lookup table?