VLOOKUP in Excel: Complete Guide with Examples
VLOOKUP is the most widely used lookup formula in Excel. It finds a value in the first column of a table and returns something from another column in the same row.
Syntax
6 VLOOKUP Examples
Basic exact match
=VLOOKUP(A2, $D$2:$F$100, 2, FALSE)Find the value in A2 inside column D, return the value from column E (2nd column of the D:F table). Absolute reference ($) so the formula can be copied down.
Wrap with IFERROR
=IFERROR(VLOOKUP(A2, $D$2:$F$100, 2, FALSE), "Not found")Returns "Not found" instead of #N/A when the lookup value is missing. Cleaner for reports and dashboards.
Handle trailing spaces
=VLOOKUP(TRIM(A2), $D$2:$F$100, 2, FALSE)TRIM removes leading/trailing spaces from the lookup value. If the table also has spaces, clean that column first with a helper column using TRIM.
Return a value from a different column
=VLOOKUP(A2, $D$2:$G$100, 4, FALSE)Returns the 4th column of the table (column G). Change the col_index to get any column in the table — always counting from the left edge of the range.
Lookup with wildcard
=VLOOKUP("*"&A2&"*", $D$2:$F$100, 2, FALSE)Finds any row where the lookup column contains the text in A2, even if surrounded by other characters. Only works with text lookups and exact-match mode (FALSE).
Two-way lookup (match row and column)
=VLOOKUP(A2, $D$2:$G$100, MATCH(B2, $D$1:$G$1, 0), FALSE)MATCH finds the column number dynamically based on a header name in B2. Inserting or reordering columns in the table will not break this formula.
Fixing VLOOKUP Errors
#N/AValue not found in the first column of the lookup table- →Check for extra spaces: wrap the lookup value in TRIM — =VLOOKUP(TRIM(A2), table, col, FALSE)
- →Check data types: if the lookup column has text "123" but you're looking up the number 123, they won't match. Convert one side.
- →Make sure the last argument is FALSE (exact match) — TRUE or 1 enables approximate match which can return wrong values
- →Wrap in IFERROR to handle missing values gracefully: =IFERROR(VLOOKUP(A2,table,2,FALSE),"Not found")
Wrong value returnedVLOOKUP is finding a partial or approximate match instead of an exact match- →The 4th argument must be FALSE for exact match. If it's TRUE or omitted, VLOOKUP uses approximate match (assumes the table is sorted)
- →Duplicate values in the first column — VLOOKUP always returns the first match from top to bottom
- →Column index number is wrong — count from the left edge of the table range, not from column A
#REF!The column index number is larger than the number of columns in the table array- →Count the columns in your table range. If the range is A:C, the maximum col_index is 3.
- →If you moved or deleted columns, the col_index may now point outside the range
- →Switch to XLOOKUP or INDEX MATCH which reference the return column directly, so insertions can't break them
#VALUE!The col_index_num argument is not a valid number- →The column index must be a positive integer. Check if a cell reference is producing a non-numeric value.
- →Do not use 0 as the column index — VLOOKUP columns start at 1
VLOOKUP Limitations You Must Know
- Only searches the first column — the lookup column must always be the leftmost column in your table_array. You cannot look up by a middle or right column.
- Only returns columns to the right — col_index_num must be 1 or greater. You cannot return a column to the left of the lookup column.
- Column number breaks on insert — if you insert a column into the table, the col_index_num no longer points to the right column. INDEX MATCH and XLOOKUP reference the return column directly and are immune to this.
- Returns the first match only — if there are duplicate values in the lookup column, VLOOKUP always returns the first match from the top. Use XLOOKUP with match_mode=-1 or use a helper column to find subsequent matches.
- Approximate match is the default — if you omit the 4th argument or use TRUE, VLOOKUP uses approximate match, which requires the first column to be sorted ascending. For almost all use cases, you want FALSE.
Should I Use VLOOKUP or XLOOKUP?
If you have Excel 365 or Excel 2021, use XLOOKUP. It solves every limitation of VLOOKUP:
- Can search any column, not just the first
- Returns columns in any direction, including left of the lookup column
- Has a built-in not-found value instead of requiring IFERROR
- Does not break when columns are inserted
- Can return multiple columns at once
Use VLOOKUP when you need compatibility with Excel 2019 or earlier, or when working in a shared file where recipients may not have XLOOKUP.
Frequently Asked Questions
What does VLOOKUP do in Excel?
VLOOKUP searches for a value in the leftmost column of a table and returns a value from a column to the right. =VLOOKUP("Apple", A2:C100, 2, FALSE) finds "Apple" in column A and returns the value from column B.
Why does my VLOOKUP return #N/A?
Most common causes: value does not exist in the lookup column, extra spaces (fix with TRIM), data type mismatch (number vs text), or missing FALSE argument for exact match.
What is the difference between VLOOKUP and XLOOKUP?
XLOOKUP can search any direction and column, does not break on column inserts, has built-in not-found handling, and can return multiple columns. Use XLOOKUP in Excel 365/2021, VLOOKUP for older versions.
Related Guides
XLOOKUP Guide
The modern replacement for VLOOKUP — learns right, returns multiple columns, no column index needed.
Read →
INDEX MATCH Guide
The power-user alternative to VLOOKUP that works in any Excel version.
Read →
VLOOKUP Not Working
Every VLOOKUP problem diagnosed and fixed — 7 causes with exact fixes.
Read →
Excel Formulas Hub
All lookup, text, date, and statistical formula categories.
Read →