How to Use VLOOKUP in Excel (With Examples and Pro Tips)

Ever had a long list of data and needed to find matching information quickly? That’s exactly what VLOOKUP in Excel is for. It lets you search for a value in one column and return a value from another column in the same row—perfect for comparing, matching, or fetching data.

By the end of this guide, you’ll know how to use VLOOKUP like a pro, from writing basic formulas to avoiding common pitfalls. Whether you’re working on a sales spreadsheet, an inventory list, or employee records, this function will save you tons of time.

Let’s dive into the practical steps and even explore how to troubleshoot and optimize your lookup formulas.

Step-by-Step Guide to Using VLOOKUP in Excel

1. Understand the VLOOKUP Syntax

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

lookup_value: What you’re searching for.
table_array: The data range that contains your lookup value and return values.
col_index_num: The column number (from the left of table_array) with the data you want returned.
range_lookup: Optional. Use FALSE for an exact match, TRUE (or omit) for approximate.

2. Prepare Your Data

Ensure the lookup column is the first column in your table.
Clean data: No merged cells, and avoid hidden spaces or formatting inconsistencies.

3. Enter a Basic VLOOKUP Formula

Example: You want to find a product’s price by its name.

=VLOOKUP("Apple", A2:C10, 3, FALSE)

This looks for “Apple” in column A (A2:A10).
Returns value from column 3 (C2:C10) in the same row.

4. Use Cell References Instead of Hardcoding

=VLOOKUP(E2, A2:C10, 3, FALSE)

If E2 contains the lookup name, you can change E2 anytime without editing the formula.

5. Lock Your Table Range (Absolute Reference)

=VLOOKUP(E2, $A$2:$C$10, 3, FALSE)

Use $ signs to make the range static when copying formulas.

6. Handle Not Found Results Gracefully

Wrap VLOOKUP in IFERROR to avoid ugly errors:

=IFERROR(VLOOKUP(E2, $A$2:$C$10, 3, FALSE), "Not Found")

7. Use Approximate Match (Optional)

=VLOOKUP(92, A2:B10, 2, TRUE)

Use when your data is sorted and you want to find the closest value less than or equal to the lookup value (e.g., grading scales).

8. Drag the Formula Down a Column

Click the bottom right corner of your cell (with the formula) and drag it down to apply to multiple rows.

Pro Tips & Workflow Improvements

✅ Use Named Ranges: Instead of $A$2:$C$10, name your range (e.g., Products) for easier reading.
⚠️ Always use FALSE for exact matches unless you know why you want approximate.
🔁 Switch to XLOOKUP: If using Excel 365 or 2019+, consider XLOOKUP, which is more flexible and intuitive.
🔍 Use Data Validation: Add dropdown lists to limit lookup values and reduce errors.
🧩 Combine with MATCH: For dynamic column selection, use MATCH to calculate the column number.

Advanced Use Case: Dynamic Column Lookup

Want to look up values across a changing column? Use MATCH with VLOOKUP:

=VLOOKUP(E2, A2:F10, MATCH(G1, A1:F1, 0), FALSE)

G1 holds the header name of the column you want.
MATCH finds the column number dynamically.

Troubleshooting & Common Mistakes

1. #N/A Error

Cause: No exact match found.
Fix: Use IFERROR, check for typos or extra spaces.

2. #REF! Error

Cause: Column index number exceeds the number of columns in your range.
Fix: Count columns correctly.

3. Incorrect Match Returned

Cause: Using approximate match (TRUE) on unsorted data.
Fix: Use FALSE for exact match or sort data.

4. Formula Doesn’t Update on Copy

Cause: Table range not locked.
Fix: Use absolute references ($A$2:$C$10).

5. Hidden Characters or Formatting Issues

Use CLEAN(), TRIM(), or check formatting to ensure clean lookups.

Conclusion

Mastering VLOOKUP transforms the way you handle spreadsheets—no more manual searching or copying. Practice with sample datasets, and soon this function will be second nature.

Next up? Try exploring INDEX + MATCH or the newer XLOOKUP function for even more flexible lookups.