How to Find Data with VLOOKUP in Excel

In large Excel spreadsheets, VLOOKUP can help you find any data

Excel's VLOOKUP function, which stands for "vertical lookup", will look up a value in the first column of a range, and return the value in any other column in the same row.

If you can't find which cell contains specific data, VLOOKUP is a very effective way to find that data. It's especially useful in very large spreadsheets where it's difficult to find information.

01
of 04

How the VLOOKUP Function Works

VLOOKUP normally returns a single field of data as its output.

How this works:

  1. You provide a name or lookup_value that tells VLOOKUP which row of the data table to look for the desired data.
  2. You supply the column number as the col_index_num argument, which tells VLOOKUP which column contains the data you seek.
  3. The function looks for the lookup_value in the first column of the data table.
  4. VLOOKUP then locates and returns the information from column number you defined in col_index_num, from the same row as the lookup value.
02
of 04

VLOOKUP Function Arguments and Syntax

Find Data in Excel with VLOOKUP
Find Price Discounts with VLOOKUP. © Ted French

The syntax for the VLOOKUP function is:

=VLOOKUP(lookup_value,table_array,col_index_num,range_lookup)

The VLOOKUP function may look confusing because it contains four arguments, but it's actually very easy to use.

The four arguments for the VLOOKUP function are as follows:

lookup_value (required): The value to search for in the first column of the table array.

table_array (required) - This is the table of data (a range of cells) that VLOOKUP searches to find the information you need.

  • The table_array must contain at least two columns of data
  • The first column must contains the lookup_value

col_index_num (required) - This is the column number of the value you want to find.

  • The numbering begins with the column 1
  • If you reference a number greater than the number of columns in the table array, the function will return the #REF! error

range_lookup (optional) - Indicates whether or not the lookup-value falls within a range contained in the table array. The range_lookup argument is either "TRUE" or "FALSE". If omitted, the value is FALSE by default.

If the range_lookup argument is TRUE, then:

  • The lookup_value is the value you want to check whether it falls inside a range defined by the table_array.
  • The table_array contains all of the ranges, and a column that contains the range value (such as high, medium, or low).
  • The col_index_num argument is the resulting range value.
03
of 04

How the Range_Lookup Argument Works

Using the optional range_lookup argument is complicated for many people to understand, so it's worth looking at a quick example.

The example in the image above uses the VLOOKUP function to find the discount rate depending on the quantity of items purchased.

The example shows that the discount for the purchase of 19 items is 2%. This is because 19 falls between 11 and 21 in the Quantity column of the lookup table.

As a result, VLOOKUP returns the value from the second column of the lookup table since that row contains the minimum of that range. Another way to set up a range lookup table could be to create a second column for the maximum, and this range would have a minimum of 11 and a maximum of 20. But the result works the same way.

In the example, the following formula containing the VLOOKUP function is used to find the discount for quantities of goods purchased.

=VLOOKUP(C2,$C$5:$D$8,2,TRUE)

  • C2: This is the lookup value, which can be in any cell in the spreadsheet.
  • $C$5:$D$8: This is fixed table containing all of the ranges you want to use.
  • 2: This is the column in the range lookup table that you want the LOOKUP function to return.
  • TRUE: Enables the range_lookup feature of this function.

Once you've pressed Enter, and the result is returned for the first cell, you can autofill the entire column to look up the range results for the rest of the cells in the lookup column.

The range_lookup argument is a very powerful way to sort out a column of mixed numbers into various categories.

04
of 04

VLOOKUP Errors: #N/A and #REF

The Excel VLOOKUP Function Returns the #REF! Error Message
VLOOKUP Returns the #REF! Error Message. © Ted French

The VLOOKUP function may return the following errors.

#N/A:

This is the "value not available" error, and is returned under the following conditions:

  • The lookup _value is not found in the first column of the table_array argument
  • The Table_array argument is inaccurate. For example, the argument may include empty columns on the left side of the range
  • The Range_lookup argument is set to FALSE, and an exact match for the lookup_value argument cannot be found in the first column of the table_array
  • The range_lookup argument is set to TRUE and all of the values in the first column of the table_array are larger than the lookup_value

#REF!: ("reference out of range") Error is Displayed If:

This is the "reference out of range" error, and is returned when the col_index_num is greater than the number of columns in the table_array.