How to Use VLOOKUP in Excel - Practical Guide

Learn how to use VLOOKUP to search for values in a table and return corresponding data from another column. This essential formula saves time on data matching tasks and is fundamental for managing large datasets efficiently in Excel.

Why This Matters

VLOOKUP is critical for data analysis, reporting, and merging information from multiple tables—skills employers value highly in finance, HR, and business intelligence roles.

Prerequisites

•Basic understanding of Excel rows and columns
•Knowledge of cell references (A1, B2, etc.)
•Familiarity with data tables and ranges

Step-by-Step Instructions

Organize your data

Ensure your lookup table has the search column on the left and return columns to the right. The lookup column must be the first column in your range for VLOOKUP to work.

Click the target cell

Select the cell where you want the VLOOKUP result to appear, typically in a column adjacent to your lookup values.

Enter the VLOOKUP formula

Type: =VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup]). Replace lookup_value with your search term, table_array with your data range, and col_index_num with the column number to return.

Set the range lookup parameter

Use FALSE or 0 for exact matches (most common), or TRUE or 1 for approximate matches. Press Enter to execute the formula.

Copy the formula down

Select the cell with your formula, copy it (Ctrl+C), then select the range below and paste (Ctrl+V) to apply VLOOKUP to all rows.

Alternative Methods

INDEX/MATCH combination

More flexible than VLOOKUP; allows searching in any column position and can work with unsorted data. Use =INDEX(return_range, MATCH(lookup_value, lookup_range, 0)).

XLOOKUP (Excel 365)

Modern replacement for VLOOKUP with simpler syntax and no column position constraints. Use =XLOOKUP(lookup_value, lookup_array, return_array).

Tips & Tricks

✓Use absolute references ($A$1:$D$100) for your table_array so the range doesn't change when copying the formula down.
✓Always sort your lookup column alphabetically or numerically if using approximate match (range_lookup = TRUE).
✓Test with a single cell first before copying the formula across hundreds of rows to catch errors early.
✓Use IFERROR to display a custom message if VLOOKUP doesn't find a match: =IFERROR(VLOOKUP(...), "Not Found").

Pro Tips

★Combine VLOOKUP with IFERROR and SMALL functions to return multiple matches instead of just the first one.
★Use wildcard characters (* and ?) in lookup values with approximate matches for flexible partial matching.
★Performance tip: For very large datasets (10,000+ rows), INDEX/MATCH often calculates faster than VLOOKUP.
★Create named ranges for your table_array to make formulas more readable and maintainable.

Troubleshooting

VLOOKUP returns #N/A

The lookup value isn't in the first column of your table. Use Ctrl+F to verify the exact value exists, check for extra spaces, and ensure data types match (text vs. number).

VLOOKUP returns wrong column

Your col_index_num is incorrect. Count your columns from left to right starting at 1, not 0. The first column is 1, the second is 2, etc.

Formula slows down Excel significantly

You're using volatile functions or entire column references (A:A). Replace with specific ranges like A1:D1000 and consider INDEX/MATCH as a faster alternative.

VLOOKUP doesn't update when data changes

Your table range may not include new data. Expand your range or use dynamic ranges with OFFSET or Table functionality.

Related Excel Formulas

INDEX MATCH XLOOKUP HLOOKUP IFERROR SUMIF

Frequently Asked Questions

Can VLOOKUP search from right to left?

No, VLOOKUP only searches left to right. If you need to search in a column that's to the left of your return column, use INDEX/MATCH instead.

What's the difference between exact and approximate match?

Exact match (FALSE/0) requires an identical match and returns #N/A if not found. Approximate match (TRUE/1) finds the largest value less than or equal to your lookup value and requires sorted data.

How do I handle VLOOKUP with duplicate values?

Standard VLOOKUP returns only the first match. Use a helper column with COUNTIF to isolate duplicates, or switch to INDEX/MATCH with additional criteria for more control.

Can I use VLOOKUP with multiple criteria?

VLOOKUP only supports one lookup column. For multiple criteria, use INDEX/MATCH with multiple conditions or SUMIFS/AVERAGEIFS functions depending on your goal.

Why is my VLOOKUP formula so slow?

VLOOKUP searches the entire range every time. Reduce the table_array size to only necessary rows, avoid entire column references (A:A), or switch to faster alternatives like INDEX/MATCH for large datasets.