Using the VLOOKUP Function in Excel

You can easily create a function to look up information!

The VLOOKUP function consists of three required arguments, in the following order: lookup value, table array, and column index number. The lookup value is the value for which you want to find matching data and must appear in the first column of the lookup table; it can be a value, a text string, or a cell reference. The table array is the name or address of the lookup table. The column index number is the number of columns Excel must count over to find the matching value.

The VLOOKUP function also has an optional fourth argument: range lookup. This can be either TRUE or FALSE. If the range lookup argument is FALSE, VLOOKUP will find only exact matches. If the range lookup argument is TRUE, or if a range lookup argument is not entered, VLOOKUP can find approximate matches. In this case, the lookup table must be sorted in ascending order by the first column in it; otherwise VLOOKUP may not return the correct value.

If the range lookup argument is TRUE or omitted and the lookup value does not appear in the first column of the lookup table, but falls between two values in it, Excel will use the lower of the two values. If the lookup value is smaller than any value in the first column of the lookup table, Excel returns an error message.

  1. Create a formula to calculate the cost of the Part Number in the spreadsheet below.  
  2. The lookup value is the Part Number in column B. 
  3. Use the Price Table in cells A17:B24 as the table array.
  4. The Price is in the second column (2) of the Price Table – that will be the column index number.
  5. If you want an exact match, you’ll use the optional range lookup argument — False.
  • Type the formula below into cell C5.  Within the parentheses, you have three required and one optional argument:  lookup value, table array (as an absolute reference to the range — even better to use a range name), the column index number (or cell reference), and True or False as the range lookup (False for an exact match).
  • Copy down the formula using AutoFill:

Questions? Contact us for more information or schedule a class for your team!

Find a Class