VLOOKUP in Excel: Find Matching Values and Fix Errors

To find a matching value in Excel, use VLOOKUP with an exact-match setting. For example, if IDs are in A2:A6, department names are in C2:C6, and the ID to search for is in E2, enter =VLOOKUP(E2,$A$2:$C$6,3,FALSE) in F2; if E2 contains 104, the result is Operations. The key rule is that the lookup values must be in the first column of the selected table range.

What VLOOKUP does

VLOOKUP is a spreadsheet lookup function: it searches vertically down the leftmost column of a range for a value, then returns information from another column in the same row. It is useful when a worksheet has a list of identifiers, product codes, names, or other keys and you want to bring related information into another table.

For example, a sales sheet might contain product IDs while a separate catalog lists each ID, product name, and price. VLOOKUP can find an ID from the sales sheet in the catalog and return the corresponding name or price. It returns a value; it does not move, merge, or permanently copy entire records. The result updates when the lookup input or source data changes, provided the formula still points to the intended range.

The function name reflects its direction: it looks vertically through rows. A basic VLOOKUP can return a value from a column to the right of the lookup column, but not from a column to its left. That limitation is important when you choose the table range and the column number.

How the formula finds a match

A VLOOKUP formula has four arguments: the value to search for, the table range to search, the number of the return column within that range, and whether to use an exact or approximate match. In the opening example, E2 is the search value, A2:C6 is the range, 3 identifies the third column of that range, and FALSE requests an exact match.

The return-column number is counted from the start of the selected range, not from the worksheet’s column letters. In A2:C6, column A is number 1, B is number 2, and C is number 3. If the selected range were B2:D6 instead, D would be number 3. This relative counting is a frequent source of wrong results after people change the range without updating the column number.

VLOOKUP returns the first matching row it finds. If the lookup column contains duplicate IDs, it will not report that the key is duplicated or automatically return every matching row; it returns the result from the first match in the range. Use unique identifiers when each record should have one unambiguous result.

Build an exact-match lookup step by step

For most lists of IDs, names, or codes, exact match is the right choice. In Excel for Microsoft 365 on Windows, enter the formula in the cell where you want the result, then replace the example references with the cells and range in your worksheet:

Put the value you want to search for in a cell, such as E2.
Select a range that starts with the lookup column and includes the column containing the result.
Count the result column from the left edge of that range.
Use FALSE as the fourth argument so Excel looks for an exact match.
Press Enter, then check the result against the source row.

The dollar signs in $A$2:$C$6 make the source range absolute. If you copy the formula down a column, the lookup reference E2 changes to E3, E4, and so on, while the source range stays fixed. You can also select the range and press F4 on Windows while editing a reference to cycle through reference styles. On Mac, the equivalent shortcut can depend on the keyboard and macOS settings; typing the dollar signs directly is a reliable alternative.

If you use a structured Excel Table, references can expand as rows are added, reducing the risk of a lookup range missing new records. The table still needs the lookup column at its left edge for VLOOKUP, and the column index must correspond to the table range you choose.

Worked example with a small employee list

Suppose A2:C6 contains this data, with the ID in the first column and the department in the third:

ID	Name	Department
101	Maya	Support
102	Liam	Finance
103	Noor	Sales
104	Ava	Operations
105	Eli	Sales

With the lookup ID 104 in E2, and the employee list in A2:C6, enter this in F2:

=VLOOKUP(E2,$A$2:$C$6,3,FALSE)

The result is Operations. Excel finds 104 in the first column, on the row containing Ava, then returns the value from the third column of that same row. To return the employee’s name instead, keep the lookup range and change the return-column number to 2; for E2 equal to 104, that returns Ava. If you fill either formula down, keep the source range fixed with dollar signs and let the lookup cell reference move to the next row.

Exact match versus approximate match

FALSE requests an exact match and is the safer default for identifiers. If Excel cannot find the requested value, it generally returns #N/A rather than choosing a nearby value. Exact match does not require the lookup column to be sorted. Text and numbers can also behave as different values: an ID stored as the number 104 may not match a text entry that looks like 104. Check the underlying cell types if a value appears identical but does not match.

TRUE requests an approximate match. It is designed for ordered ranges such as tax bands, discount thresholds, or grading cutoffs. The first column must be sorted in ascending order; Excel returns an exact match if available, or otherwise the result for the largest lookup value that is less than the search value. If the data is unsorted, approximate lookup can return a misleading answer. Omitting the fourth argument also uses approximate matching, so include FALSE explicitly when you need an exact match.

For example, suppose A2:B5 contains ascending minimum scores and grades: 0/F, 60/D, 70/C, and 80/B. With a score of 76 in E2 and those thresholds in A2:B5, this formula returns C:

=VLOOKUP(E2,$A$2:$B$5,2,TRUE)

That result means the score falls at or above 70 but below the next threshold, 80. A threshold table must be designed around that rule; it is not simply a way to find the closest number in either direction.

Common errors and how to fix them

#N/A: With FALSE, this usually means there is no exact match. Check for leading or trailing spaces, inconsistent punctuation, different number-versus-text storage, or an incorrect lookup value. Also confirm the value is in the range’s first column.
#REF!: The return-column number is greater than the number of columns in the selected range, or a referenced range has been deleted. Recount the range columns and adjust the index.
Wrong result: Check whether the last argument is TRUE or omitted, whether the lookup list is sorted, and whether duplicate keys cause the first matching row to be returned.
Formula changes when copied: Lock the source range with dollar signs, while leaving the lookup cell relative if each row should search for a different value.
Numbers that look alike do not match: A value may be stored as text in one place and as a number in another. Apply a consistent data type to both columns rather than changing the lookup mode to approximate.

When a lookup value may legitimately be absent, an error-handling function can display a readable message instead of #N/A. With the same employee list in A2:C6, the lookup ID in E2, and the formula entered in F2, use:

=IFERROR(VLOOKUP(E2,$A$2:$C$6,3,FALSE),"Not found")

If E2 is 104, the result remains Operations; if no matching ID is found, the result is Not found. IFERROR also catches errors other than #N/A, so use it thoughtfully: a message can make an output easier to read, but it can also hide a broken range or an invalid column number. During setup, it can be helpful to leave the underlying error visible until the lookup is verified.

VLOOKUP limitations and practical safeguards

VLOOKUP’s left-to-right restriction is its defining limitation. If the value to return sits to the left of the lookup column, rearranging source data may not be practical. The fixed numeric column index is another weakness: inserting a column inside the lookup range can change which field a number points to, potentially producing a valid-looking but incorrect result. Review formulas after changing a source table’s layout.

Other safeguards make lookup workbooks more dependable:

Use a stable, unique key such as an employee ID instead of a name that may be repeated or spelled differently.
Keep lookup values and source keys in consistent formats, and clean imported whitespace when needed.
Use an exact match for IDs, account numbers, and product codes.
Make the source range wide enough to include the return field but no wider than necessary.
Test a known match, a missing value, and any duplicate-key situation before relying on the results.

VLOOKUP is not case-sensitive for ordinary text matching, so values such as “abc” and “ABC” are generally treated as equivalent. If capitalization itself distinguishes records, VLOOKUP alone is not a suitable case-sensitive lookup method. Also, a formula result is not a permanent snapshot: edits to the source data can change the returned value.

Alternatives: XLOOKUP and INDEX with MATCH

For Microsoft 365 and Excel 2021 or later, XLOOKUP can search one range and return a corresponding value from another, including a column to the left. With IDs in A2:A6, departments in C2:C6, and the lookup ID 104 in E2, this formula returns Operations:

=XLOOKUP(E2,A2:A6,C2:C6,"Not found")

XLOOKUP makes the lookup range and return range separate arguments, so it does not require a numbered return column. It also uses exact matching by default, and its optional not-found result can replace an error message. It is a practical choice for newer workbooks, but VLOOKUP may be needed when a file must work in older Excel versions that do not support XLOOKUP.

INDEX with MATCH is another option, including in older versions. With IDs in A2:A6, departments in C2:C6, and 104 in E2, this formula returns Operations:

=INDEX(C2:C6,MATCH(E2,A2:A6,0))

MATCH with 0 requests an exact match and supplies the position of the ID; INDEX returns the value at that position in the department range. This combination can look up to the left or right because the lookup and return ranges do not have to be part of one left-to-right block. For a straightforward table with the lookup column on the left, however, VLOOKUP remains a useful, widely recognized option.

Frequently Asked Questions

Why does VLOOKUP return #N/A when the value is visibly there?
Check whether one entry is stored as text and the other as a number, whether either contains extra spaces, and whether the value is in the first column of the selected range. Also verify that the final argument is FALSE for an exact lookup and that the formula points to the correct cells.

Can VLOOKUP search from right to left?
No. The value being searched for must be in the leftmost column of the lookup range, and VLOOKUP returns a value from a column to its right. Use XLOOKUP in supported versions or INDEX with MATCH if you need to return a value from a column to the left.

What does the number 3 mean in a VLOOKUP formula?
It means return the value from the third column of the selected range. It does not mean worksheet column C unless the range begins in column A. Count from the range’s left edge each time.

Should I use TRUE or FALSE for VLOOKUP?
Use FALSE for an exact match, which is appropriate for most keys and identifiers. Use TRUE only for a deliberately sorted threshold table where an approximate result is intended. Leaving out the final argument can select approximate matching, so explicitly state the intended choice.

Can VLOOKUP return more than one matching row?
No. It returns the first match it encounters in the lookup column. If you need every matching row, use a filtering approach such as FILTER in Microsoft 365 or Excel 2021 and later, or use Excel’s filtering tools to display all rows that meet the condition.

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

More in Errors & fixes

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

Spotted an error in this guide? Let us know.