=VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup]) VLOOKUP searches vertically down the first column of a table and retrieves data from a specified column in the matching row. Always set range_lookup to FALSE (or 0) for exact match.
VLOOKUP Function in Excel & Google Sheets
VLOOKUP (Vertical Lookup) is one of the most widely used functions in business spreadsheets, allowing you to connect and merge disparate data tables using a common identifier.
1. Step-by-Step Exact Match Syntax
=VLOOKUP(lookup_value, table_array, col_index_num, FALSE)Example Product Catalog (Cells A1:D5):
| Product ID (Col 1) | Item Name (Col 2) | Category (Col 3) | Unit Price (Col 4) |
|---|---|---|---|
| SKU-401 | Ergonomic Office Chair | Furniture | $249.99 |
| SKU-402 | 27-inch 4K Monitor | Electronics | $389.00 |
| SKU-403 | Mechanical Keyboard | Peripherals | $89.50 |
| SKU-404 | USB-C Hub 7-in-1 | Accessories | $45.00 |
To retrieve Unit Price for SKU-402:
=VLOOKUP("SKU-402", A2:D5, 4, FALSE)- Returns:
$389.00
2. Preventing Breakage with Absolute References ($)
When dragging a VLOOKUP formula down a long column, always lock the table_array with dollar signs ($A$2:$D$5):
=VLOOKUP(F2, $A$2:$D$5, 2, FALSE)Without $, the lookup table shifts down on each row, causing #N/A errors for lower rows.
3. Wrapping with IFERROR for Clean Reports
To replace ugly #N/A errors with a custom message or blank cell:
=IFERROR(VLOOKUP(F2, $A$2:$D$5, 4, FALSE), "Not in Catalog")4. VLOOKUP from Another Sheet
To pull data from a sheet named MasterData:
=VLOOKUP(A2, MasterData!$A$2:$D$500, 3, FALSE)Frequently Asked Questions
Why does my VLOOKUP return #N/A when the value is clearly in the table?
Common causes include: extra hidden spaces in text cells (fix with TRIM), numeric vs text data type mismatch, or forgetting to set the 4th argument to FALSE.
Can VLOOKUP look to the left?
No. VLOOKUP can only search the leftmost column of table_array and return columns to its right. Use XLOOKUP or INDEX/MATCH to look left.
What happens if there are duplicate matches in the table?
VLOOKUP will always return the FIRST match it encounters scanning top-to-bottom and ignore subsequent duplicates.