VLOOKUP in Excel: Steps, Examples, and Common Fixes

To use VLOOKUP in Excel, put the value you want to find in a cell, then enter a formula such as =VLOOKUP(E2,$A$2:$C$5,2,FALSE) in another cell. This exact-match example searches the first column of A2:C5 for the value in E2 and returns the matching row’s value from the second column.

VLOOKUP steps in Excel

In Microsoft Excel for Microsoft 365 on Windows, follow these steps to look up a value in a table:

Arrange the source data in columns, with the values to search for in the leftmost column of the lookup range.
Enter the value to find in a cell, or identify the cell that already contains it.
Choose a result cell outside the source table and type =VLOOKUP(.
Enter the lookup value or its cell reference, then a comma.
Select the full table range, including both the lookup column and the column containing the result. Add dollar signs to lock the range if you plan to copy the formula.
Enter a comma, then the number of the result column counted from the left edge of the selected range.
Enter FALSE for an exact match, close the parenthesis, and press Enter.
Check the result against the source data. If you copy the formula to other rows, confirm that the lookup reference changes as intended while the table range stays fixed.

For example, with the lookup value in E2 and a source table in A2:C5, a formula returning the second column is =VLOOKUP(E2,$A$2:$C$5,2,FALSE). If E2 contains 1044 and that ID appears in the first column, the formula returns the corresponding value from column B.

What VLOOKUP means and how it works

VLOOKUP means “vertical lookup.” It searches down the first column of a selected range and, when it finds a matching value, returns a value from another column in the same row. The function does not search every column for the lookup value; the search column must be the range’s leftmost column.

Its syntax is VLOOKUP(lookup_value,table_array,col_index_num,[range_lookup]). The four arguments have distinct jobs:

lookup_value: The value, text, or cell reference Excel should search for.
table_array: The range containing the lookup column and the return column.
col_index_num: The position of the result column within that range, starting at 1 for the leftmost column.
range_lookup: FALSE requests an exact match; TRUE requests an approximate match. This last argument is optional, but it is safer to specify it rather than rely on the default.

Column numbers are relative to the selected range, not the worksheet. If your range is C:F, then C is column 1, D is 2, E is 3, and F is 4. Selecting a range that starts in column C and entering 4 therefore returns from worksheet column F.

A worked VLOOKUP example

Suppose A2:C5 contains employee IDs, names, and sales totals, in that order: 1042, Lina, 680; 1043, Omar, 725; 1044, Priya, 640; and 1045, Mateo, 810. If E2 contains 1044, this formula returns the name from the second column:

With IDs in A2:A5, names in B2:B5, sales in C2:C5, and the requested ID in E2, enter =VLOOKUP(E2,$A$2:$C$5,2,FALSE). The result is Priya.

To return the sales total instead, change the column index from 2 to 3. With the same layout and E2 equal to 1044, enter =VLOOKUP(E2,$A$2:$C$5,3,FALSE). The result is 640. The range still starts at column A, so C is the third column within it.

The dollar signs in $A$2:$C$5 make the range absolute. If you copy the formula down, Excel can adjust E2 to E3 while keeping the source table fixed. Without those dollar signs, the table range may shift and point to the wrong rows.

Exact match or approximate match?

Use FALSE for most everyday lookups, such as finding a customer, product code, employee ID, or invoice number. Exact match means Excel must find the requested value in the first column; if it cannot, the result is usually #N/A. The search column does not have to be sorted for an exact-match lookup.

Approximate match, selected with TRUE, is useful when the first column contains sorted breakpoints, such as tax bands, grading thresholds, or commission rates. Excel finds the largest value in that first column that is less than or equal to the lookup value. The first column must be sorted in ascending order for reliable results. If it is not, the answer can be incorrect without an obvious warning.

For a concrete threshold example, suppose A2:B5 contains minimum scores and labels: 0 / Needs review, 60 / Pass, 70 / Merit, and 80 / Distinction. With a score of 76 in E2, enter =VLOOKUP(E2,$A$2:$B$5,2,TRUE). The result is Merit, because 70 is the greatest threshold not above 76. If you want only a score that exactly matches a threshold, use FALSE instead.

Prepare the data before looking it up

Many lookup problems come from the source data rather than the formula. Before troubleshooting, check that the values in the lookup column are consistent and that the selected table range includes every needed row and result column.

Check number and text formats. A numeric ID stored as text may not match the same digits stored as a number. Make both sides the same data type; changing the display format alone may not convert the underlying value.
Look for extra spaces or hidden characters. Pasted data can contain leading or trailing spaces. Cleaning the source values can resolve an otherwise puzzling #N/A.
Check duplicates. If the lookup column contains the same key more than once, VLOOKUP returns the first matching row it encounters. It does not report every match.
Keep the lookup field on the left. If the value to search for is not the first column of the range, rearrange the data or use another lookup method.
Use a stable range. Absolute references help when copying formulas. For a changing dataset, an Excel table can expand as rows are added, reducing the chance that new records fall outside a fixed range.

Common VLOOKUP errors and how to fix them

#N/A means Excel did not find a match in the first column, especially with an exact lookup. Confirm that the value exists, that the lookup column is included at the left edge of the range, and that numbers and text have compatible types. Also check for spaces, punctuation differences, and a misspelled lookup value.

#REF! commonly appears when the column index is larger than the number of columns in the selected range. For instance, an index of 4 cannot return a result from a range containing only three columns. Count from the range’s left edge and make sure the range includes the desired result column.

Wrong result without an error: Check whether the last argument is TRUE or omitted. Approximate match can return a nearby threshold rather than an exact value. Also verify that the first column is sorted if approximate matching is intentional, and remember that duplicate exact keys return the first matching row.

If you want a more readable message when an exact lookup fails, wrap the lookup in IFERROR. With IDs in A2:A5, names in B2:B5, and the requested ID in E2, enter =IFERROR(VLOOKUP(E2,$A$2:$B$5,2,FALSE),"Not found"). Excel displays the matching name when available and Not found if the formula produces an error. Use this carefully: IFERROR can hide issues other than a missing match, so investigate unexpected errors while building the workbook.

VLOOKUP limitations and alternatives

VLOOKUP is convenient when the lookup field is the first column of a table and the result is to its right. Its key limitation is that it cannot return a value from a column to the left of the lookup column. Its numeric column index can also become outdated if someone inserts or rearranges columns within the selected range.

In Microsoft 365 and Excel 2021 or later, XLOOKUP can search one range and return a value from a separate range, including a column to the left. With IDs in A2:A5, names in B2:B5, and the requested ID in E2, enter =XLOOKUP(E2,A2:A5,B2:B5,"Not found"). It returns the matching name and can specify what to display when no match exists. XLOOKUP is not available in older perpetual Excel versions, so VLOOKUP can be more suitable when a workbook must work with those releases.

Another option is INDEX with MATCH, which can look in one column and return from another, including a column to the left. It is useful for compatibility and flexibility, but its two-function structure may be less straightforward for new users. For a simple left-to-right lookup, VLOOKUP remains a practical choice; for a new workbook in a version that supports it, XLOOKUP is often easier to adapt.

Using VLOOKUP on Mac and in Excel for the web

The VLOOKUP function and its basic arguments work in Excel for Microsoft 365 on Windows, Excel for Mac, and Excel for the web. The ribbon and formula entry experience can differ slightly by platform, but the formula examples above do not require a Windows-only feature. Excel for the web supports editing formulas, although some workbook tools and interface details differ from the desktop app.

Keyboard shortcuts can vary by operating system and keyboard settings. On Windows, press Enter to confirm a formula. On Mac, press Return. Formula separators can also depend on regional settings: if Excel expects semicolons instead of commas, use the separator shown by your installation when entering arguments.

Frequently Asked Questions

Why does VLOOKUP return #N/A when the value is there?
Excel may see the lookup value and source value as different data types, or one may contain an extra space or hidden character. Confirm the lookup column is the first column in the selected range, use FALSE for exact matching, and compare the underlying values.

Can VLOOKUP look to the left?
No. VLOOKUP searches the leftmost column of its table range and returns a value from a column to its right. Use XLOOKUP in Microsoft 365 or Excel 2021 and later, or use INDEX with MATCH, when the result is to the left.

What does the number 2 mean in a VLOOKUP formula?
It is the return-column index counted within the selected range. If the range begins in A and includes A:C, 2 means the second column, B. It does not mean worksheet column 2 in every formula.

Do I need to sort data for VLOOKUP?
Not for an exact lookup using FALSE. For approximate matching with TRUE, sort the first column in ascending order; otherwise Excel may return an unreliable result.

What happens if there are duplicate lookup values?
VLOOKUP returns the first matching row in the lookup range. If you need to review all rows with the same key, use a filtering approach rather than relying on a single VLOOKUP result.

Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.

More in Formulas & functions

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.