VLOOKUP Guide: Syntax, Errors & Fixes

VLOOKUP is an Excel and Google Sheets function that looks for a value in the first column of a table and returns a matching value from a column to the right. You use it when you need to pull related information from another part of your spreadsheet, such as finding an employee’s department based on their ID or looking up a product price from a code. This guide explains how it works, how to write it correctly, and why it sometimes returns errors.

What VLOOKUP Actually Does

VLOOKUP stands for “vertical lookup.” It searches down the first column of a specified range until it finds a cell that matches your search key, then it returns the value from a column you specify in the same row. The function always searches left-to-right: the lookup column must be the leftmost column in the range you provide, and the return column must be to the right of it.

For example, if you have a table with product codes in column A and prices in column B, you can use VLOOKUP to find the price for a code you type into a cell. The function will scan column A for an exact match, then return the corresponding value from column B.

VLOOKUP Syntax and Arguments

The basic syntax in both Excel and Google Sheets is:

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

Here is what each argument means:

• lookup_value – the value you want to search for. It can be a number, text, or a cell reference.
• table_array – the range of cells that contains the data, including the lookup column and the return column.
• col_index_num – the column number in the range from which to return a value. Column 1 is the first column of the range, column 2 is the second, and so on.
• range_lookup – optional. Use FALSE for an exact match, or TRUE (or omit) for an approximate match. For most real-world lookups, you should use FALSE.

A common example: =VLOOKUP(A2, C:D, 2, FALSE) looks for the value in cell A2 within column C, and returns the matching value from column D.

Exact Match vs. Approximate Match

The fourth argument determines whether VLOOKUP finds an exact match or an approximate one. This is the most common source of confusion.

Exact match (FALSE) – VLOOKUP scans the first column for a value that exactly equals your lookup value. If it finds one, it returns the corresponding value. If not, it returns #N/A. This is what you should use for IDs, names, codes, and most other lookups.

Approximate match (TRUE or omitted) – VLOOKUP finds the largest value in the first column that is less than or equal to your lookup value. This requires the first column to be sorted in ascending order. It is useful for things like tax brackets, discount tiers, or shipping rates where you want to match a value to a range. For example, if your table has income thresholds in column A and tax rates in column B, an approximate match will return the rate for the highest threshold that is still below your income.

If you use approximate match without sorting the first column, results will be unpredictable. Always sort the lookup column ascending when using TRUE.

Common VLOOKUP Errors and How to Fix Them

VLOOKUP returns several error values when something goes wrong. Here are the most frequent ones and what they mean:

• #N/A – The lookup value was not found. Check for extra spaces, different data types (text vs. number), or a value that genuinely does not exist in the table.
• #REF! – The col_index_num is larger than the number of columns in the range. For example, if your range is A:B and you ask for column 3, you get this error.
• #VALUE! – The col_index_num is less than 1, or the lookup value and the first column contain incompatible data types.
• Incorrect results, no error – This happens when you use approximate match without sorting, or when there are duplicate lookup values. VLOOKUP only returns the first match it finds.

To troubleshoot, first verify that the lookup value exists in the first column of your range. Use the TRIM function to remove extra spaces from both the lookup value and the data. Ensure that numbers are stored as numbers, not text. If you are copying the formula down, check that the table range is locked with absolute references like $A$1:$B$100 so it does not shift.

VLOOKUP vs. INDEX-MATCH and XLOOKUP

VLOOKUP has limitations: it cannot look to the left, it breaks if you insert a column in the table, and it only returns the first match. Two alternatives address these issues.

INDEX-MATCH is a combination of two functions: INDEX returns a value from a specific row and column, and MATCH finds the row number of a lookup value. The formula looks like =INDEX(return_range, MATCH(lookup_value, lookup_range, 0)). This allows you to search any column, not just the first one, and it is more flexible when you add or remove columns.

XLOOKUP is a newer function available in Excel 365 and recent versions of Google Sheets. It simplifies the syntax to =XLOOKUP(lookup_value, lookup_range, return_range) and supports exact match by default, left lookups, and returning multiple values. If your version supports it, XLOOKUP is generally easier to use and less error-prone.

For most new spreadsheets, XLOOKUP is recommended. For older files or compatibility with older Excel versions, VLOOKUP remains a reliable choice.

Best Practices for Using VLOOKUP

To get consistent results and avoid errors, follow these guidelines:

• Always use FALSE for exact match unless you specifically need an approximate match.
• Make sure the lookup column is the leftmost column in your table range.
• Use absolute references (with $) for the table range when copying formulas.
• Keep your data clean: remove duplicate lookup values, trim spaces, and ensure consistent formatting.
• Consider using a helper column if you need to combine multiple conditions, since VLOOKUP cannot handle multiple criteria directly.
• If you are looking up values that might not exist, wrap VLOOKUP in IFERROR to display a friendly message like “Not found” instead of #N/A.

Frequently Asked Questions

Why does VLOOKUP return #N/A even though the value is in the table?

This usually happens because of a data type mismatch. If your lookup value is a number but the first column contains numbers stored as text, or vice versa, VLOOKUP will not recognize them as equal. Check the cell formatting and use the VALUE or TEXT function to convert, or manually re-enter the numbers. Extra spaces before or after the value can also cause this.

Can VLOOKUP look to the left?

No, VLOOKUP only searches the leftmost column of the range and returns values to the right. If you need to look up a value in a column that is not the first, use INDEX-MATCH or XLOOKUP instead.

How do I use VLOOKUP with multiple criteria?

VLOOKUP cannot handle multiple criteria directly. One workaround is to create a helper column that combines the criteria with a separator, such as =A2&"-"&B2, and then use that helper column as the lookup column. Alternatively, use INDEX-MATCH with an array formula or XLOOKUP with concatenated ranges.

Does VLOOKUP work in Google Sheets?

Yes, VLOOKUP works in Google Sheets with the same syntax and behavior. The main difference is that Google Sheets may handle array formulas differently, but the basic function is identical.

What is the difference between VLOOKUP and HLOOKUP?

VLOOKUP searches vertically down a column, while HLOOKUP searches horizontally across a row. HLOOKUP is used when your lookup value is in the top row of a table and you want to return a value from a lower row. It is less commonly used because data is usually organized vertically.

Can I use VLOOKUP to return multiple columns?

VLOOKUP returns only one value per formula. To return multiple columns, you can copy the formula across and change the col_index_num for each column, or use an array formula like =VLOOKUP(A2, A:D, {2,3,4}, FALSE) in Excel 365, which spills results into adjacent cells.

Related

• Best Mil-Sim Extraction Shooters to Play Instead of Gray Zone Warfare
• Industrial vs Humanoid Robots for Factory Automation
• MW4 Incoming Achievement Trophy Guide