How to Use VLOOKUP in Excel: Step-by-Step Guide

You need to find information from a large table in Excel. Manually searching is slow and error-prone. The VLOOKUP function automates this search across columns. This guide will show you how to use VLOOKUP to find data quickly and accurately.

Understanding the VLOOKUP Function

VLOOKUP stands for Vertical Lookup. It searches for a value in the first column of a specified range. When it finds a match, it returns a value from a different column in the same row. You need four pieces of information to build the formula.

The lookup value is what you are searching for. The table array is the range of cells containing your data. The column index number tells Excel which column to pull the result from. The range lookup argument controls whether you want an exact or approximate match.

VLOOKUP Syntax and Arguments

The function syntax is VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup]). The lookup_value can be text, a number, or a cell reference. The table_array must include the column with your lookup values and the column with your results. The col_index_num is a number, where 1 is the first column in your table_array. The optional range_lookup is either TRUE for an approximate match or FALSE for an exact match.

Steps to Perform an Exact Match VLOOKUP

An exact match VLOOKUP is the most frequent use. It finds a specific item like a product ID or employee name. Follow these steps to set one up.

1. Organize your data table
   Ensure your lookup table is organized with the search key in the leftmost column. The data you want to retrieve should be in a column to the right.
2. Click the cell for your result
   Select the cell where you want the VLOOKUP answer to appear. This is usually in a separate report or summary sheet.
3. Start the VLOOKUP formula
   Type an equals sign (=) followed by VLOOKUP and an opening parenthesis. The formula should look like this: =VLOOKUP(
4. Enter the lookup_value
   Click on the cell that contains the value you want to find. For example, click on cell A2 containing a part number. Type a comma to separate the arguments.
5. Define the table_array
   Select the entire range of your lookup table, including the lookup column and the result column. Press F4 once to add absolute references (dollar signs). This keeps the range fixed if you copy the formula. Type another comma.
6. Enter the col_index_num
   Count the columns in your table_array from the left. Enter the number of the column that holds the data you want to return. Type a comma.
7. Set range_lookup to FALSE
   Type FALSE or 0 to perform an exact match search. Close the formula with a parenthesis and press Enter.

Steps to Perform an Approximate Match VLOOKUP

An approximate match finds a value within a numeric range. It is used for grading scales, discount brackets, or tax rates. Your lookup table’s first column must be sorted in ascending order.

1. Prepare a sorted lookup table
   Create a table where the first column contains the minimum values for each range. For example, 0, 1000, 5000 for sales commission tiers. Sort this column from smallest to largest.
2. Build the VLOOKUP formula
   Start the formula with =VLOOKUP( and click the cell with your numeric value, like a sales amount. Add a comma.
3. Select the sorted table array
   Highlight your entire lookup table and press F4 to make it absolute. Add a comma.
4. Enter the column index number
   Type the number for the column containing the result, like the commission rate. Add a final comma.
5. Set range_lookup to TRUE
   Type TRUE or 1 as the last argument. Close the parenthesis and press Enter. Excel will find the largest value in the first column that is less than or equal to your lookup value.

Common VLOOKUP Mistakes and How to Avoid Them

#N/A Error Appears in the Result Cell

The #N/A error means Excel cannot find your lookup value. The most common cause is a mismatch in data. Your lookup value might have extra spaces, or the data in the table might be a different data type. Use the TRIM function to remove spaces. Ensure numbers are not stored as text. Verify you are using FALSE for an exact match.

#REF! Error in the Formula

A #REF! error means the col_index_num is greater than the number of columns in your table_array. For example, your table_array has 3 columns, but you entered 4 as the index number. Re-count the columns in your selected range. The index number must be 1 or greater but not exceed the total columns.

VLOOKUP Returns the Wrong Data

This happens when the col_index_num points to the wrong column. Double-check that you counted the columns from the first column of your table_array, not from column A of the worksheet. Also, using TRUE for an approximate match on an unsorted table will return incorrect results. Always sort the first column when using TRUE.

Formula Returns a Value from the Wrong Row

If your table has duplicate values in the first column, VLOOKUP will only find the first match from the top. It will not find subsequent duplicates. You must ensure your lookup column contains unique identifiers, or use a different function like XLOOKUP or INDEX with MATCH.

Exact Match vs Approximate Match VLOOKUP

Item	Exact Match VLOOKUP	Approximate Match VLOOKUP
Primary Use	Finding specific text or codes like SKUs or names	Finding values within numeric ranges like tax brackets
Range_lookup Argument	FALSE or 0	TRUE or 1
Data Sorting Requirement	Not required	First column must be sorted ascending
Common Error for Missing Value	#N/A	Returns value for next lowest threshold
Performance	Slower on very large unsorted tables	Faster, uses binary search algorithm

You can now use VLOOKUP to find data in your Excel tables automatically. Practice with both exact and approximate match types. For more complex lookups to the left of your key column, explore the XLOOKUP function. Remember to press F4 to lock your table_array reference before copying the formula down a column.