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

Key Takeaways

• VLOOKUP searches the first column of a data range and returns a value from any column to the right, making it one of Excel's most useful lookup functions.

• The VLOOKUP formula requires four arguments: lookup_value, table_array, col_index_num and range_lookup (TRUE or FALSE).

• Setting the range_lookup argument to FALSE ensures an exact match, which is the most common use case.

• Common VLOOKUP errors like #N/A and #REF! are usually caused by mismatched data types, incorrect column numbers or unsorted data.

What Is VLOOKUP?

Have you ever needed to look up current prices for an invoice for goods shipped? What about looking up the price discount for a loyal customer?

VLOOKUP stands for "Vertical Lookup," and it is one of Excel's most widely used functions, alongside essentials like the SUM formula. A VLOOKUP formula searches down the first column of a table for a specific value and then returns a corresponding value from another column in the same row. It is the go-to tool whenever you need to pull data from one table into another, whether you are matching product prices, employee IDs, customer discount tiers or any other paired information.

Learning how to use VLOOKUP is one of the most practical Excel skills you can develop. The function's precise parameter requirements can seem intimidating at first, and a small error can produce unpredictable results, but you'll find it easy to use once you understand the syntax and follow a simple step-by-step process.

VLOOKUP Syntax and Arguments

Before building your first formula, it helps to understand the VLOOKUP syntax and what each argument does. The basic structure looks like this:

=VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])

Here is a quick overview of each argument:

• lookup_value: The value you want to search for in the first column of your data table. This can be a cell reference, text string or number.

• table_array: The range of cells that contains your source data. The lookup_value must appear in the first column of this range.

• col_index_num: The column number (starting from one) in the table_array from which to return a value. For example, if your table has three columns and you want the value from the third column, enter three.

• range_lookup: An optional argument that controls match type. Enter FALSE for a VLOOKUP exact match (the most common choice) or TRUE for a VLOOKUP approximate match. If you leave this blank, Excel defaults to TRUE.

With this foundation in place, you are ready to build a VLOOKUP formula step by step.

Set Up the Data

First enter your source data. This is the list from which Excel will look up some value. In this example, because Excel will need to look up a product name and find the price, the source is the price list. On each row, enter one product per row, with the product name in Column A and its price in Column B.

Be sure to sort the data in alphabetical order to ensure VLOOKUP can operate properly. If your data is not sorted, you can use the Sort button on the Data ribbon.

To reduce the chance of reference errors, give your data table a range name.

Create the Formula

To look up a product price, type "=VLOOKUP(A4,Sheet2!$a$2:$b$10,2,FALSE)".

The parameters are:

• lookup_value: The value to find in the table. This value must appear in the first column of the source data. In this example, the lookup_value is the product name, which is in A4 on the invoice.

• table_array: The source data. In this example, the table_array is the price list that you created in the previous step. Be sure to use an absolute reference for the table if you plan to write this formula once and copy it to multiple cells. If you use a relative reference, your table_array reference will change, which will lead to unpredictable (and incorrect) results.

• col_index_num: The column that holds the value you are looking for. In this example, because your formula is looking up the product price, col_index_num is the number of the column that contains the prices. Note that it is a column number, not a letter. The column number is the position in your table.

• [range_lookup]: This is an optional parameter, as indicated by the square brackets, meaning that you may leave it blank. However, to be sure that the VLOOKUP formula functions as you expect, it's a good practice to enter TRUE or FALSE. The only difference is when your VLOOKUP tries to find a product that does not exist in the price list.

o If TRUE, VLOOKUP will return the price for the last product that comes before the invalid product name in alphabetical order.

o If FALSE, VLOOKUP will return a #N/A error if the product does not exist in the price list.

Check the Results

Because VLOOKUP can be tricky to use, never assume it is returning the correct answer. Take a couple of minutes to look up some values yourself and verify that the results are correct.

In this example, the t-shirt price is showing $9.50. A quick glance back at the price list shows that $9.50 is, in fact, the correct price.

Common VLOOKUP Errors and How to Fix Them

Even experienced Excel users run into VLOOKUP errors. Here are the most common issues and how to resolve them:

• #N/A error: This means VLOOKUP cannot find the lookup_value in the first column of your table_array. Common causes include typos, extra spaces, mismatched data types (such as a number stored as text) or a range_lookup set to FALSE when no exact match exists. Use the TRIM function to remove hidden spaces and IFERROR to handle the error gracefully.

• #REF! error: This occurs when the col_index_num exceeds the number of columns in your table_array. For example, if your table has only two columns and you enter three as the col_index_num, Excel returns #REF!. Double-check your table range and column number.

• #VALUE! error: This appears when the col_index_num is less than one or the lookup_value exceeds 255 characters. Verify that your column number is a positive integer and that your lookup value is within the character limit.

• Incorrect result returned silently: This is the trickiest problem because Excel does not flag it as an error. It happens when range_lookup is left blank or set to TRUE and your data is not sorted in ascending order. VLOOKUP performs an approximate match and may return the wrong value without warning. Always set range_lookup to FALSE unless you specifically need an approximate match with properly sorted data.

If your VLOOKUP is not working and you cannot identify the cause, start by checking these four issues in order. Most problems trace back to one of them.

VLOOKUP Tips and Best Practices

Follow these VLOOKUP tips to write reliable formulas and avoid common mistakes:

• Always use absolute references ($) for the table_array when copying formulas to multiple cells. This prevents the range from shifting and returning incorrect results.

• Set range_lookup to FALSE unless you specifically need an approximate match. This eliminates an entire category of silent errors.

• Keep the lookup column as the first column of your table_array. VLOOKUP can only search the leftmost column, so structure your data accordingly.

• Use named ranges for your data tables. Named ranges improve readability and reduce the chance of reference errors, especially in large workbooks.

• Watch for hidden spaces or inconsistent formatting in lookup values. A single trailing space can cause a VLOOKUP exact match to fail. Use TRIM and CLEAN to standardize your data before running lookups.

• Sort data in ascending order if using approximate match (TRUE). VLOOKUP relies on sorted data to perform approximate matches correctly. Unsorted data produces unreliable results.

VLOOKUP vs. XLOOKUP and INDEX/MATCH

VLOOKUP is not the only lookup function available in Excel. Depending on your version of Excel and the complexity of your task, you may want to consider XLOOKUP or INDEX/MATCH instead.

VLOOKUP remains valuable to learn because it appears in millions of existing spreadsheets across every industry. If you work with shared files or legacy workbooks, you will encounter VLOOKUP regularly. For new formulas, consider XLOOKUP if your version of Excel supports it. XLOOKUP searches in any direction, defaults to exact match and lets you specify a custom value when no match is found. INDEX/MATCH offers the most flexibility and performs well on large datasets, making it a strong choice for users ready to move beyond the basics.

Next Steps

The VLOOKUP function has many other uses, from looking up prior months' results to replacing current prices with proposed prices to see the overall change or grouping numeric data into layers. For any of these applications, follow the same process: set up the data table, write the function and test the results.

For more advanced applications, try exploring XLOOKUP or INDEX/MATCH for greater flexibility, or move your data into a PivotTable, one of the most powerful tools available in Excel. HLOOKUP works exactly like VLOOKUP except that it searches across the top row instead of down the left column, which can be useful for horizontally structured data.

Ready to sharpen your Excel skills beyond VLOOKUP? Pryor Learning offers hands-on Excel training courses and seminars that cover lookup functions, PivotTables, advanced formulas and more. Explore Pryor Learning's full catalog to find the right course for your skill level.