How to Use VLOOKUP in Excel: Step-by-Step Guide

The VLOOKUP (Vertical Lookup) function is the backbone of data merging in Excel and Google Sheets. Whether you are reconciling invoices, searching for employee IDs, or matching product prices, mastering VLOOKUP will save you hours of manual work.

VLOOKUP Syntax Explained

The VLOOKUP formula follows this structure:

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

• lookup_value — the value you want to search for (e.g., A2 or "Apple")
• table_array — the entire range containing both the lookup column and the result column (e.g., $A$2:$D$100)
• col_index_num — the column number in the table_array that holds the return value (1 = first column, 2 = second, etc.)
• range_lookup — FALSE for exact match, TRUE for approximate match. Always use FALSE for IDs and exact values.

Real-World VLOOKUP Example: Employee Lookup

Imagine you have an employee table in cells A2:D10:

| A          | B          | C          | D          |
|------------|------------|------------|------------|
| EmployeeID | FirstName  | LastName   | Department |
| 101        | Alice      | Johnson    | Sales      |
| 102        | Bob        | Smith      | Marketing  |
| 103        | Carol      | Davis      | Engineering|

To find Carol's department using her employee ID 103 in cell G2:

=VLOOKUP(G2, A2:D10, 4, FALSE)

This tells Excel: "Find 103 in column A, go to the 4th column of the range (column D), and return the value from that row." The result is "Engineering".

Step-by-Step VLOOKUP Walkthrough

1. Identify the lookup value. Put the value you want to search for in a cell (e.g., type 103 into G2).
2. Select the table array. Highlight the entire data range including both the lookup column and the return column. Always use absolute references like $A$2:$D$10 so the range doesn't shift when you copy the formula.
3. Count the return column. In our table, Department is the 4th column of the range A2:D10, so col_index_num = 4.
4. Set match type to FALSE. For exact matches on IDs, names, or codes, always use FALSE. Use TRUE only for grade brackets, tax tables, or price tiers where you want the nearest match.
5. Press Enter and verify. The formula returns the department. Copy it down to look up all employees at once.

Common Pitfalls to Avoid

• The Left-to-Right Rule: VLOOKUP can only search for a value in the leftmost column of your range. If your lookup value is to the right of your result, VLOOKUP won't work — use XLOOKUP or INDEX/MATCH instead.
• Approximate vs. Exact Match: Always use FALSE or 0 as the last argument for exact matches (IDs, names, SKUs). Using TRUE on unsorted data returns random-looking results.
• Static Column Index: Hardcoding 3 as col_index_num breaks if you insert a new column in the table_array. Use named ranges or switch to XLOOKUP for dynamic column references.
• Missing Absolute References: Without $ signs (e.g., $A$2:$D$10), copying the formula down shifts the table array and produces wrong results.
• Data Type Mismatch: A number stored as text (e.g., "123" vs 123) will cause #N/A. Use VALUE() or TEXT() to align types.

VLOOKUP vs XLOOKUP vs INDEX MATCH

Bottom line: Use VLOOKUP for quick one-off lookups in stable tables. Use INDEX MATCH when you need flexibility or lookups to the left. Use XLOOKUP if you have Excel 365 — it is the most powerful and intuitive option.

Using VLOOKUP Across Multiple Sheets

You can VLOOKUP into another worksheet by referencing the sheet name in the table_array:

=VLOOKUP(A2, 'Sheet2'!$A$2:$D$100, 4, FALSE)

For a different workbook (assuming it is open):

=VLOOKUP(A2, '[SalesData.xlsx]Orders'!$A$2:$D$100, 4, FALSE)

Important: If you close the external workbook, the formula continues to work with cached values. Re-open the source file to refresh.

5 VLOOKUP Edge Cases You Should Know

• #N/A when value exists: The lookup value might have trailing spaces. Wrap lookup_value in TRIM(): =VLOOKUP(TRIM(A2), $A$2:$D$10, 4, FALSE).
• Matching partial text with wildcards: Use * for any sequence of characters: =VLOOKUP("*"&TRIM(A2)&"*", $A$2:$D$10, 4, FALSE). This finds cells that contain the search term anywhere.
• VLOOKUP returning 0 instead of blank: If the result cell is empty, VLOOKUP returns 0. Wrap with IF: =IF(VLOOKUP(...)="", "", VLOOKUP(...)) or use IFERROR for a cleaner fallback.
• VLOOKUP with dynamic column index using MATCH: Combine VLOOKUP with MATCH to make the column index dynamic: =VLOOKUP(A2, $A$2:$D$10, MATCH("Department", $A$1:$D$1, 0), FALSE). This finds "Department" in the header row and returns the correct column number automatically.
• VLOOKUP with IFERROR for clean output: Wrap VLOOKUP in IFERROR to display a friendly message instead of #N/A: =IFERROR(VLOOKUP(A2, $A$2:$D$10, 4, FALSE), "Not Found").