Excel lookup functions tutorial

Here are simple instructions on how to use Excel's LOOKUP functions. With Vertical lookup (VLOOKUP) you can search a value from columns (top to bottom) and return values from other cells in the same row. With horizontal lookup (HLOOKUP) you can search rows from left to right and return values from cells below the matched cell.

VLOOKUP example


Look at the table below. How can we find matching product names for the codes on the right (1003, 1010, 1020 & 1018)? 

1. Select the cell in which you want to display the product name and click the fx button under the ribbon. Search for the VLOOKUP (vertical lookup) function.

Why VLOOKUP? We need to use vertical lookup because the product codes are in the same column, so we are searching codes from up to bottom. If the product codes were in the same row, we would use HLOOKUP.



Excel lookup function tutorial

Excel lookup function tutorial


2. To the lookup_value choose the product code.

Excel lookup function tutorial

3. To the Table_array choose both columns A and B, because column A holds the product code and column B holds the corresponding name.

Excel lookup function tutorial

4. Column_index_num we put the number of the column that holds the information we are looking for. In this case it is number 2, because of the selected are the second column has the product name.

Excel lookup function tutorial

5. In to Range_lookup we write FALSE, because we want to find exact match for our product code. If we don't have an exact match for our code, we could search for the closest match by typing TRUE.

Excel lookup function tutorial

6. To get the Price + VAT for the product, we again use the product code to find the corresponding price. This time need to select all of the columns in between column A and column B.You cannot skip columns.

Excel lookup function tutorial

7. Col_index_num is 5 this time.

Excel lookup function tutorial

8. Write false to Range lookup and hit OK. Drag copy your results down to get the following.

Excel lookup function tutorial


If you want to do it manually, here is the syntax:


Get the name of the product:
=VLOOKUP(G2;A:B;2;FALSE)
Note: depending on your regional settings, you might have to use comma (,) instead of semicolon (;) as a separator.

Get the price + VAT:

=VLOOKUP(G2;A:E;5;FALSE)
Note: depending on your regional settings, you might have to use comma (,) instead of semicolon (;) as a separator.

  1. Lookup value is G2
  2. Table array is A:E
  3. Column index number is 5
  4. Range lookup is false, as we want the exact match

HLOOKUP example


Horizontal lookup function (HLOOKUP) works exactly the same, it just searches from left to right.


To find which project we have to work on Thursday, use the following function:
=HLOOKUP("Thursday";1:2;2;FALSE)
This will give us Project D.

Note: depending on your regional settings, you might have to use comma (,) instead of semicolon (;) as a separator.

  1. Lookup value is "Thursday"
  2. Table array are rows 1:2
  3. Column index number is 2
  4. Range lookup is false, as we want the exact match

Range lookup Example

Range lookup parameter can be used to find the exact match for the lookup value OR a closest value from the top row. So as an example, we have a shop that offers discount to clients that buy more stuff (look at image below). If a customer bought things for total cost of 541 euros and we wanted to find out which discount category we belong in (look at image below):




=HLOOKUP(541;1:2;2;TRUE)

This would return us 3 % as 500 is the closest match on the top row. 

No comments:

Post a Comment