How to Use VLOOKUP in Excel (and Google Sheets): A Step‑by‑Step Guide
1. What Is VLOOKUP and When Should You Use It?
VLOOKUP (vertical lookup) searches the first column of a table‑array for a specified lookup value and returns a value from the same row in a column you choose.
=VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])
| Argument | Description |
|---|---|
| lookup_value | Value to find (text, number, or reference). |
| table_array | Range that contains the data; the first column is where the search occurs. |
| col_index_num | Column number (starting at 1) within table_array to return. |
| range_lookup | Optional. FALSE (or 0) = exact match; TRUE (or omitted) = approximate match on sorted data. |
Typical Use Cases
- Pull related data – e.g., retrieve a product’s price from a master list.
- Standardize entries – convert short codes (NY) to full names (New York).
- Build reports – combine transaction data with a master reference table.
When VLOOKUP Is the Right Tool
| Situation | Works well |
|---|---|
| Lookup column is the leftmost column of the table | ✅ |
| You need a single value per lookup | ✅ |
| Data set is moderate (hundreds to a few thousand rows) | ✅ |
| Using Excel 2007‑2023 or Google Sheets | ✅ |
If the lookup column is not the first column, or you need to return a value to the left, consider INDEX/MATCH or XLOOKUP (Excel 365/2021).
2. Setting Up a Reliable Lookup Table
A clean, contiguous table makes VLOOKUP fool‑proof.
| A | B | C |
|---|---|---|
| ID | Employee Name | Department |
| 101 | Alice Johnson | Marketing |
| 102 | Brian Smith | Sales |
| 103 | Carla Reyes | Finance |
| 104 | Daniel Wu | HR |
Key tips
- Leftmost column must contain the lookup values.
- No blank rows/columns inside the range.
- Avoid merged cells – they break references.
- Consistent data types – numbers vs. text.
- Optional: give the range a name (e.g.,
EmployeeTable = A2:C5) for readability.
Simple Example
Find the department for ID 102 (cell E2) and place the result in F2:
=VLOOKUP(E2, $A$2:$C$5, 3, FALSE)
Result: Sales.
3. Syntax and Arguments in Detail
VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])
| Argument | Required? | Notes |
|---|---|---|
| lookup_value | Yes | Can be a cell reference, text, number, or formula. |
| table_array | Yes | First column is where Excel searches. |
| col_index_num | Yes | Must be ≤ number of columns in table_array. |
| range_lookup | No | FALSE (exact) is safest; TRUE requires sorted data. |
Important points
- Exact vs. approximate –
FALSEprevents accidental matches on the nearest smaller value. - Column index limits – VLOOKUP cannot return a column to the left of the lookup column. Use
INDEX/MATCHorXLOOKUPfor that. - Compatibility – Works in all modern Excel versions and Google Sheets with identical syntax.
4. Practical Examples (Excel & Google Sheets)
Example 1 – Retrieve a product name from its ID
| A | B | C |
|---|---|---|
| ID | Product | Price |
| 101 | Notebook | 2.50 |
| 102 | Pen | 0.80 |
| 103 | Stapler | 5.75 |
| 104 | Paper Pack | 3.20 |
Goal: ID in E2, return product name in F2.
=VLOOKUP(E2, $A$2:$C$5, 2, FALSE) // works in Excel 2007‑2023 and Google Sheets
If E2 = 102, the formula returns Pen**.
Example 2 – Pull the price for a product name
Goal: Product name in G2, return price in H2.
=VLOOKUP(G2, $B$2:$C$5, 2, FALSE) // works in Excel 2007‑2023 and Google Sheets
If G2 = "Stapler", the formula returns 5.75**.
5. Common Errors & Quick Fixes
| Error | Meaning | Quick Fix |
|---|---|---|
| #N/A | Lookup value not found in first column. | Check spelling, trim spaces, or use TRIM()/UPPER(). |
| #REF! | col_index_num exceeds table width. |
Count columns in the array; adjust the index. |
| #VALUE! | Invalid col_index_num (e.g., non‑integer) or bad range_lookup. |
Use a whole number for the index; set fourth argument to FALSE or 0. |
| #NAME? | Function name misspelled or unavailable. | Verify spelling (VLOOKUP). In older Excel, ensure the Analysis ToolPak is enabled. |
| Wrong result | range_lookup set to TRUE on unsorted data. |
Either sort the first column ascending or change the fourth argument to FALSE. |
Typical Pitfalls
- Mismatched data types – number stored as text vs. numeric lookup. Convert with
VALUE()orTEXT(). - Hidden spaces –
"Apple "won’t match"Apple". Clean withTRIM(). - Left‑hand lookups – VLOOKUP can’t search a column to the right of the return column; use
INDEX/MATCHorXLOOKUPinstead.
6. Tips for More Robust VLOOKUP
| Tip | How It Works | Example |
|---|---|---|
| Force exact match | Prevents nearest‑match errors. | =VLOOKUP(D2, A2:B5, 2, FALSE) |
Wrap with IFERROR |
Returns a friendly message instead of #N/A. |
=IFERROR(VLOOKUP(D2, A2:B5, 2, FALSE), "Not found") |
Lookup from the right with CHOOSE |
Creates a virtual two‑column array where the lookup column isn’t leftmost. | =VLOOKUP("Cherry", CHOOSE({1,2}, B2:B5, A2:A5), 2, FALSE) |
Dynamic column index with MATCH |
Keeps the formula working if columns move. | =VLOOKUP("Banana", A2:C5, MATCH("Price", A1:C1, 0), FALSE) |
| Two‑criteria lookup | Concatenate keys to create a unique identifier. | =VLOOKUP(A2&"|"&B2, $D$2:$F$6, 3, FALSE) |
7. FAQ
What is the difference between VLOOKUP and INDEX/MATCH?
VLOOKUP can only search the leftmost column and returns values to the right, while INDEX/MATCH lets you look up in any column and return a value from any other column (including to the left). INDEX/MATCH is also faster on large data sets and works with unsorted data without the approximate‑match risk.
How can I perform a case‑sensitive lookup in Excel?
Combine VLOOKUP with the exact‑match operator EXACT inside an IF array formula, or use FILTER/XLOOKUP with the 0 match mode. A common pattern is:
=INDEX(return_range, MATCH(TRUE, EXACT(lookup_range, lookup_value), 0))
Enter it with Ctrl+Shift+Enter in legacy Excel (or just press Enter in Excel 365/2021).
Can I use VLOOKUP in Google Sheets?
Yes. Google Sheets supports the same syntax and arguments as Excel. The function behaves identically, though Google Sheets does not have the newer XLOOKUP function, making VLOOKUP a useful fallback for many scenarios.
Bottom line: VLOOKUP remains a straightforward way to pull related data when the lookup column is leftmost and you need a single result per row. Master the exact‑match mode, guard against common errors, and you’ll be able to retrieve data quickly in both Excel and Google Sheets.