VLOOKUP Formula Builder
Fill in the fields — get a ready-to-paste Excel formula
A cell reference like B2, or a value in quotes like "John" or 1001
to
Count columns from the left edge of your table
e.g. if your table starts at column D — D=1, E=2, F=3 …
Your VLOOKUP formula
=VLOOKUP( … fill in the fields above … )
Want to master VLOOKUP and 50+ Excel functions?
View course →
Step-by-step Excel course — from basics to advanced formulas, pivot tables, and dashboards.
Frequently Asked Questions
-
What does VLOOKUP stand for and what does it do?VLOOKUP stands for Vertical Lookup. It searches down the first column of a table for a value you specify, then returns a value from a different column in the same row. It is one of the most used Excel functions for joining data across tables — for example, finding an employee's salary by their ID, or a product's price by its code.
-
What is the VLOOKUP formula syntax?
=VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])
lookup_value — what you are searching for (a cell reference or value)
table_array — the range containing your data
col_index_num — which column of the table to return (1 = leftmost column)
range_lookup — FALSE for exact match, TRUE for approximate -
When should I use FALSE vs TRUE?Use FALSE (exact match) in almost every everyday case — looking up an employee ID, invoice number, product code, or name. Use TRUE (approximate match) only when your lookup column contains sorted numeric ranges, such as tax slabs or grade boundaries, where you want the closest value below the lookup.
-
Why should I lock the table range with $ signs?When you drag a VLOOKUP formula down a column, Excel adjusts all cell references automatically. Without $ signs, the table range shifts with each row and breaks the formula. Using
$D$2:$H$100instead ofD2:H100keeps the table fixed. Always lock the table range when dragging the formula. -
What does IFERROR do in a VLOOKUP?When VLOOKUP cannot find the lookup value it returns a
#N/Aerror. Wrapping it in IFERROR replaces that error with any message you choose:=IFERROR(VLOOKUP(...), "Not found")
This keeps your spreadsheet clean and is best practice in any real-world file. -
What is the difference between VLOOKUP and XLOOKUP?XLOOKUP is the modern replacement available in Excel 365 and Excel 2021. It can look left (VLOOKUP can only look right), handles errors natively without IFERROR, and does not need a column number. If you have Excel 365, XLOOKUP is generally better. If you share files with users on older Excel versions, stick with VLOOKUP for compatibility.