What is the syntax for VLOOKUP in Excel?

The syntax for VLOOKUP is: =VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])

What are common errors with VLOOKUP?

#N/A when value not found. #REF! when column index exceeds range

What is VLOOKUP in Excel and how do I use it?

VLOOKUP (Vertical Lookup) searches for a value in the first column of a table and returns a value from a specified column in the same row. The syntax is =VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup]). For example, =VLOOKUP("Apple", A2:C10, 2, FALSE) finds "Apple" in column A and returns the value from column B (the 2nd column).

Why is VLOOKUP not finding a value that exists?

Common reasons VLOOKUP fails to find existing values: 1) Extra spaces - use TRIM() on your data; 2) Different data types - numbers stored as text or vice versa; 3) Hidden characters - use CLEAN() to remove them; 4) Case doesn't matter for VLOOKUP, but formatting might; 5) The lookup value is not in the FIRST column of your table_array.

Can VLOOKUP look to the left?

No, VLOOKUP cannot look to the left. It only searches the leftmost column of your table_array and returns values from columns to the right. To look left, use: 1) XLOOKUP (Excel 365/2021); 2) INDEX/MATCH combination; 3) Rearrange your data so the lookup column is on the left. INDEX/MATCH is the classic solution: =INDEX(A:A, MATCH(value, B:B, 0)).

How do I fix #N/A error in VLOOKUP?

To fix #N/A errors in VLOOKUP: 1) Wrap in IFERROR: =IFERROR(VLOOKUP(...), "Not Found"); 2) Check for extra spaces with TRIM; 3) Ensure data types match (use VALUE() or TEXT()); 4) Verify the lookup value exists in the first column; 5) Make sure you're using FALSE for exact match. The #N/A error means the value wasn't found in your lookup range.

Lookup & Reference

VLOOKUP Function in Excel

Looks for a value in the leftmost column of a table, and then returns a value in the same row from a column you specify.

Syntax

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

Arguments

lookup_value (required): The value to search for in the first column
table_array (required): The range containing the data
col_index_num (required): The column number to return from
range_lookup (optional): TRUE for approximate match, FALSE for exact match

Examples

=VLOOKUP("Apple", A2:C10, 2, FALSE) - Find exact match for Apple and return column 2 - Result: $1.50
=VLOOKUP(A2, Products!A:D, 3, FALSE) - Cross-sheet lookup - Result: Category

Overview

VLOOKUP is still the most searched Excel lookup. This guide covers exact-match syntax, real table layouts, when to switch to XLOOKUP, and how to fix #N/A without hiding real data problems.

Copy-paste scenarios (exact match)

Product price from ID: =VLOOKUP(G2,$A$2:$D$500,4,FALSE) — column 4 is Price when A=ID.
Employee department: =VLOOKUP(H2,$A$2:$C$200,3,FALSE).
Tax rate from customer code: lock table_array with $ before filling down.

When VLOOKUP is the wrong tool

Return column is to the left of the key → use [XLOOKUP](/functions/xlookup/) or [INDEX MATCH](/blog/index-match-excel-guide/).
You need all matching rows → use [FILTER](/functions/filter/) in Microsoft 365.
Shared file opens in Excel 2019 → keep VLOOKUP until everyone has Excel 2021/M365.

Audit before you trust the column

Spot-check 3 known IDs manually after building the formula.
Add a helper =EXACT(TRIM(G2),TRIM(A2)) on problem rows.
Full diagnostic: [VLOOKUP returning #N/A](/problems/vlookup-returning-na/).

Quick answer: vlookup excel

VLOOKUP finds a value in the first column of a table and returns a value from a column to the right. Use FALSE for exact match on IDs and codes.
Formula: =VLOOKUP(lookup_value, table_array, col_index_num, FALSE)
Put the lookup column as the leftmost column in table_array.
Use FALSE (or 0) for exact match on business keys.
If you see #N/A, check spaces, text vs number, and col_index_num after column inserts.

When to use VLOOKUP

Use VLOOKUP when maintaining older Excel workbooks or when compatibility with Excel 2019 and earlier is required.
Use FALSE for exact match unless you intentionally need an approximate match on sorted data.
Keep the lookup column as the first column in the table_array to avoid #N/A or wrong results.

VLOOKUP vs XLOOKUP decision table

Use VLOOKUP when the workbook must open in Excel 2019 or older, or when an existing model already depends on table_array layout.
Use XLOOKUP when you need a left lookup, a built-in not-found message, or a return column that is not to the right of the key.
Use INDEX MATCH when you maintain a legacy two-step lookup model or need a two-way intersection lookup.
Use FILTER when you need all matching rows to spill, not just the first match.

Sample lookup tables you can recreate

Price list: ProductID in column A, ProductName in B, Price in D — formula =VLOOKUP(G2,$A$2:$D$500,4,FALSE).
Employee directory: EmployeeID in column A, Department in C — formula =VLOOKUP(H2,$A$2:$C$200,3,FALSE).
Invoice lookup: CustomerCode in column A, TaxRate in E — lock the table with absolute references before copying down.

Fix #N/A and wrong results

Confirm FALSE for exact match on IDs, codes, and labels.
Clean spaces with TRIM and CLEAN before comparing values.
Review [VLOOKUP returning #N/A](/problems/vlookup-returning-na/) and [XLOOKUP vs VLOOKUP](/compare/xlookup-vs-vlookup/).

Step-by-step: first VLOOKUP in 5 minutes

1. Put lookup IDs in column G starting at G2.
2. Build table_array with the lookup column first (e.g. $A$2:$D$500).
3. Set col_index_num to the return column position (4 = column D).
4. Use FALSE for exact match.
5. Copy down and spot-check three known IDs before trusting the column.

Common errors

#N/A when value not found
#REF! when column index exceeds range

Use cases

Data retrieval
Cross-referencing tables
Report generation

Frequently asked questions

What does FALSE mean in VLOOKUP? FALSE (or 0) forces an exact match. Use it for IDs, SKUs, and codes. TRUE is approximate match and requires a sorted first column.
Why must the lookup column be first in the table? VLOOKUP only searches the leftmost column of table_array. Rearrange columns or switch to XLOOKUP if the key is not leftmost.
VLOOKUP vs XLOOKUP for a new file? Use XLOOKUP in Excel 2021/M365 for left lookups and cleaner syntax. Keep VLOOKUP when the workbook must open in Excel 2019.
What is VLOOKUP in Excel and how do I use it? VLOOKUP (Vertical Lookup) searches for a value in the first column of a table and returns a value from a specified column in the same row. The syntax is =VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup]). For example, =VLOOKUP("Apple", A2:C10, 2, FALSE) finds "Apple" in column A and returns the value from column B (the 2nd column).
Why is VLOOKUP not finding a value that exists? Common reasons VLOOKUP fails to find existing values: 1) Extra spaces - use TRIM() on your data; 2) Different data types - numbers stored as text or vice versa; 3) Hidden characters - use CLEAN() to remove them; 4) Case doesn't matter for VLOOKUP, but formatting might; 5) The lookup value is not in the FIRST column of your table_array.
Can VLOOKUP look to the left? No, VLOOKUP cannot look to the left. It only searches the leftmost column of your table_array and returns values from columns to the right. To look left, use: 1) XLOOKUP (Excel 365/2021); 2) INDEX/MATCH combination; 3) Rearrange your data so the lookup column is on the left. INDEX/MATCH is the classic solution: =INDEX(A:A, MATCH(value, B:B, 0)).
How do I fix #N/A error in VLOOKUP? To fix #N/A errors in VLOOKUP: 1) Wrap in IFERROR: =IFERROR(VLOOKUP(...), "Not Found"); 2) Check for extra spaces with TRIM; 3) Ensure data types match (use VALUE() or TEXT()); 4) Verify the lookup value exists in the first column; 5) Make sure you're using FALSE for exact match. The #N/A error means the value wasn't found in your lookup range.

Editorial review

Reviewed by Excel.Directory Editorial Team. Updated May 2026.