VLOOKUP Generator
Build VLOOKUP formulas step by step. Perfect for beginners and experts alike.
How VLOOKUP Works
Build Your VLOOKUP
The cell containing the value to search for
The range containing your lookup table (first column must contain the lookup values)
Column number in the table to return (1 = first column, 2 = second, etc.)
Use exact match unless you're looking up in sorted ranges
Your VLOOKUP Formula
=VLOOKUP(A2, D2:F100, 2, FALSE)Copy this formula and paste it into Excel. It will look up the value in A2, search for it in the first column of D2:F100, and return the value from column 2.
Common VLOOKUP Mistakes to Avoid
- •The lookup value must be in the first column of your table range
- •Column index starts at 1, not 0 (1 = first column of the range)
- •Use FALSE for exact matches to avoid unexpected results
- •Consider using $ signs (e.g., $D$2:$F$100) to lock the table range when copying
How to Use VLOOKUP in Excel
VLOOKUP is one of the most useful functions in Excel. It searches for a value in the first column of a range and returns a value from another column in the same row.
VLOOKUP Syntax
=VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])
- lookup_value - The value to search for
- table_array - The range containing your data
- col_index_num - The column number to return
- range_lookup - TRUE for approximate match, FALSE for exact match
VLOOKUP vs XLOOKUP
If you have Excel 365 or Excel 2021, consider using XLOOKUP instead. It's more flexible and doesn't require the lookup value to be in the first column. However, VLOOKUP is still useful for backward compatibility with older Excel versions.