How to use VLOOKUP in Excel

VLOOKUP is a powerful Excel function that allows you to look for a specified value in one column of data inside a table, and then fetch a value from another column in the same row.

An example where VLOOKUP might be useful is if you have a monthly sales report in Excel, and want to find the sales made by a specific salesperson from within a monthly sales report. You would lookup the the person's name in the Salesperson column, and then look in the Sales column to find that person's sales for the month.

In this lesson you'll learn how to use VLOOKUP in your spreadsheets. We'll take you through several simple examples where you can see VLOOKUP in action.

Note - Microsoft have announced a new function, XLOOKUP, which improves on VLOOKUP. It is only available to users of Office365 at the moment, but you can read our lesson on how to use XLOOKUP here..

Scenarios where VLOOKUP is useful

VLOOKUP is useful for the following scenarios - no doubt you can think of examples of your own:

• Calculating the commission for a sales employee, given the value or quantity of sales they have made (look up the salesperson's name, find their sales, and multiply by the commission rate).
• Deciding which commission rate to pay based on the level of sales (look up the actual sales in a commission table to find the appropriate commission percentage to pay).
• Looking up the price of a given product from a table of product information (look up the product name or part number and return the price for that product)
• Looking up the price of a product for a given sales quantity (look up the number of items being ordered and find the price to be charged for that volume).
• Checking the date an employee started work, given the employee's staff ID number (look up the staff ID number and return their start date)..

The VLOOKUP function is particularly useful as an alternative to using multiple nested IF statements, particularly once you have more than two or three nested IF functions in your formula. Formulas with multiple IF statements can get very complicated. Often, a long formula with lots of IF functions can be replaced by a single VLOOKUP formula.

VLOOKUP() Function Syntax

The VLOOKUP() function has the following syntax:

=VLOOKUP(lookup_value, lookup_table_range, column, exact)

VLOOKUP() works by taking the value you are looking up (lookup_value) and looking for it in the first column of the table you are searching in (lookup_table_range).

Once the function finds the matching value in the lookup_table_range, it then reads across that row the table to the column you chose and returns the value it finds there.

In some cases you want the VLOOKUP function to find the nearest match, and sometimes you'll want the exact match. In this function, exact is an optional value (that's why it's not shown in bold above). if you set the exact parameter to 1 the formula will look for the nearest value. If you set it to 0 (or leave it out), it will look for an exact match and return a #N/A error if it can't find an exact match.

Examples of the VLOOKUP() function in action - VLOOKUP with an exact match

Here is an example of how the VLOOKUP() function is used to find a value based on an exact match.

• Suppose you have a table that looks like the following:
  
• You've been asked to come up with a way to check the price of a product when a product ID is typed into a given cell
• The formula you type in looks like this:
  
• In the table above, the VLOOKUP function looks for the value found in B20 (9823) in the first column of the range of cells from A6:C10 (i.e. the table in the previous step).
• Having found it in the first column (column A) it then reads across to the third column (column C) and retrieves the value. In this case, the value retrieved is 13.
• Some points to note about this scenario:
  • In case you're wondering, the $ signs in the function ($A$6:$C$10) mean that if you copy the formula in B21 to another location, it will continue to look in the product table. This is an example of absolute references in action; find out more about absolute references here. Note that if your VLOOKUP function generates a #N/A error, it could be because you didn't use absolute references, and then copied and pasted the VLOOKUP formula from one cell to another
  • No doubt you're sharp enough to have spotted the 0 as the exact, or fourth, parameter in the function in B21. This means that the VLOOKUP function is looking for an exact match. If it hadn't found the value from B20 (9823) in the first column of the table, the formula in B21 would have returned a #N/A error, meaning Excel couldn't return a value from the function. Remember, of course, that the exact parameter is optional, and defaults to 0 if you don't include it, so in this case we could have left this value out altogether and got the same result.
• If you want to become a VLOOKUP() ninja, here's something important you need to know about the exact parameter in the VLOOKUP function:
  • I'm sure you noticed this already, but did you see that the items in the Product ID table are not sorted in any order.
  • When using the VLOOKUP function, you need to check if the column you're performing the VLOOKUP on is sorted in ascending order (i.e. smallest values first, which means 1,2,3,4 or A,B,C,D). Sorting them in descending order (largest values first) isn't the same.
  • If the first column in your lookup table isn't sorted, you MUST use the exact version of the VLOOKUP function, as we did in this example. This forces the VLOOKUP() function to find an exact match. If an exact match cannot be found, the function will return a #N/A error.
  • If we had put 1 instead of 0 in our formula in the example above, the VLOOKUP function would have looked down the list in column A until either it found the value it was looking for, or it found a value that was larger than the value it was looking for, and then taken the previous value in column A as the nearest match. More on "nearest matches" with the VLOOKUP function in the next example.

Examples of the VLOOKUP() function in action - VLOOKUP with the nearest match

Here is an example of how the VLOOKUP() function is used to find a value based on an exact match.

• Suppose you have a table that looks like the following:
  
• This table represents a scenario where an order between 1 and 9 units will cost 20 per unit. An order between 10 and 49 units will cost 18 per unit, and so on.
  • It's important that you understand this before you go onto the next step, otherwise you'll get confused and the result the VLOOKUP function returns.
  • Note that in this scenario, any order over 200 units will cost 12 per unit.
  • We have also assumed that the minimum order quantity is greater than 0. However, the VLOOKUP function has no problem with using negative numbers if that's what your particular situation requires.
• In this example, you've been asked to come up with a way to check the price of a product for a given sales quantity. The method you choose should work for any sales quantity that is entered.
• You set up a VLOOKUP formula as follows:
  
• The formula works by looking up the value in B18 (23) in the first column of the table (column A) in the previous step.
• Because the VLOOKUP() function is using the nearest match, it goes down column A looking for a match with the value in B18. In this example, it doesn't find one, and eventually it reaches the value in A8 (50) which the first value larger than the value it is looking for (23). At this point the VLOOKUP function stops looking and moves to the next step.
  • This is why, for VLOOKUP with a nearest match, you MUST sort the lookup table so the first column is in ascending order. If you don't, VLOOKUP may stop looking too soon, and ignore the possibility that a better (or exact) match existed further down the table.
• The VLOOKUP function then drops back to the previous row (10) and reads across the table to the second column and retrieves the value it finds (18).
• It's worth noting that it is very common when using the VLOOKUP table to get the design of the lookup table wrong. If your VLOOKUP function isn't working properly, check the design of the table first to make sure that it is correct.

As I said earlier, the VLOOKUP() function in Excel is very powerful. These are two examples of how to use it, but there are many more ways it can be applied. If you have a scenario where you think the VLOOKUP() function might apply but you can't figure it out, or you have another situation where you've successfully used the VLOOKUP() function in Excel, why not tell us about it the comments below?