How does the VLOOKUP Formula Generator turn a column letter into an index?

It subtracts the first column of your table range from the letter you typed and adds one. With a table that starts in column B, the return column D becomes index 3. If the letter is left of the table, the tool explains that VLOOKUP cannot look left and suggests INDEX MATCH.

What does the live test in the VLOOKUP Formula Generator actually evaluate?

It runs your formula's options against the small sheet on the page using the same rules a spreadsheet applies: exact match ignores case and honours wildcards, approximate match is a binary search that expects sorted data, empty return cells come back as zero, and mismatched types give #N/A.

Should I choose IFERROR or IFNA when wrapping my VLOOKUP?

IFNA hides only the not-found error, so a wrong column index or a broken range still shows up. IFERROR hides every error, which is handy for reports but can mask mistakes. The generator offers both and shows the result of each in the live test.

When is the approximate match option safe to use in a VLOOKUP?

Only when the first column is sorted ascending and you want the closest value that does not exceed the lookup value, such as a score-to-grade table. The generator warns you, and the test grid flags unsorted data because the answer can then be wrong without any error.

Why do the XLOOKUP and INDEX MATCH versions appear below my VLOOKUP?

They return the same cell for the same inputs but avoid VLOOKUP's limits: they can look left and do not break when a column is inserted. Copy whichever matches the Excel or Google Sheets version your colleagues use.

Does the VLOOKUP Formula Generator send my table or formula anywhere?

No. The formula text and the sample cells are handled in your browser only. Your last settings and the cells of the test grid are remembered in local storage on your device so the page reopens as you left it.

VLOOKUP Formula Generator

Name: VLOOKUP Formula Generator
Author: ArrayKit

Describe the lookup once and get a correct VLOOKUP for Excel or Google Sheets, with IFERROR, XLOOKUP and INDEX MATCH versions and a live test, all in your browser.

The formula builder and the live test run entirely in your browser. The lookup values, ranges and sample cells you type are never uploaded, and nothing is sent to ArrayKit.

Built and maintained by Udit Agarwal

VLOOKUP, INDEX MATCH and XLOOKUP, Explained

Open the INDEX MATCH Formula Generator

About VLOOKUP Formula Generator

Most broken VLOOKUPs fail for the same few reasons: the range is not anchored, the column index is off by one, approximate match is running on unsorted data, or a number is being compared with text. This generator asks for the lookup value, the table range and the return column, then writes the formula with the argument order and quoting already right. Type a column letter instead of a number and it is converted relative to the table start. Add IFERROR or IFNA, TRIM, a text-or-number conversion or a wildcard with one click, and switch the argument separator to a semicolon for European locales. Below the formula, a small editable sheet evaluates it with real VLOOKUP rules and shows #N/A honestly. Everything is computed on your device; no cells or formulas are uploaded.

Features

Writes VLOOKUP with the correct argument order, quoting and doubled quotes inside text
Return column by number or by letter, converted relative to the table's first column
One-click $ locking, whole-column ranges and a bounded rows 2 to 100 range
IFERROR or IFNA wrapper with your own not-found text
TRIM, text-as-number, number-as-text and wildcard options for messy lookup values
Equivalent XLOOKUP, INDEX MATCH, FILTER and QUERY formulas, each with a Copy button
Comma or semicolon separators for Excel and Sheets in any locale
Live test grid with exact, approximate, wildcard and unsorted-data behaviour, plus a checklist for #N/A

How to use the VLOOKUP Formula Generator

Enter the lookup value as a cell such as F2, or type the text or number to find
Enter the table range with the lookup column first, then lock it with the dollar-sign button
Choose the return column by number or letter and pick exact or approximate match
Add IFERROR, TRIM or a separator change if you need them, then copy the formula
Check the result on the sample sheet and read the checklist if it shows #N/A

Example

Input

Lookup value:  F2  (the cell contains 103)
Table range:   $A$2:$D$100
Return column: 3  (Price)
Match type:    Exact
If not found:  IFERROR with "Not found"

Output

=IFERROR(VLOOKUP(F2,$A$2:$D$100,3,FALSE),"Not found")

XLOOKUP:      =IFERROR(XLOOKUP(F2,$A$2:$A$100,$C$2:$C$100),"Not found")
INDEX MATCH:  =IFERROR(INDEX($C$2:$C$100,MATCH(F2,$A$2:$A$100,0)),"Not found")
Live test on the sample table: 103 returns 4.00

The same lookup written three ways, with the cherry row highlighted in the live test.

Common errors & troubleshooting

VLOOKUP returns #N/A although the value is visibly in the table. — The two values are probably different types or have hidden spaces. A number stored as text never equals a real number, and a trailing space makes a different value. Turn on the TRIM option, or convert one side with the number and text options.
The formula works in the first row and fails further down. — The table range was not locked, so it slid down as you filled the formula. Press the Lock $ button so the range reads $A$2:$D$100 and fill down again.
VLOOKUP returns the wrong row on a table that is not sorted. — Approximate match assumes the first column is sorted ascending and does a binary search. Switch to exact match for IDs, names and codes, and keep approximate match for ranges such as tax brackets.
Excel reports #REF! after the table range changed. — The column index is larger than the number of columns in the range. The generator checks this and tells you the table width before you copy the formula.

Frequently asked questions

How does the VLOOKUP Formula Generator turn a column letter into an index?: It subtracts the first column of your table range from the letter you typed and adds one. With a table that starts in column B, the return column D becomes index 3. If the letter is left of the table, the tool explains that VLOOKUP cannot look left and suggests INDEX MATCH.
What does the live test in the VLOOKUP Formula Generator actually evaluate?: It runs your formula's options against the small sheet on the page using the same rules a spreadsheet applies: exact match ignores case and honours wildcards, approximate match is a binary search that expects sorted data, empty return cells come back as zero, and mismatched types give #N/A.
Should I choose IFERROR or IFNA when wrapping my VLOOKUP?: IFNA hides only the not-found error, so a wrong column index or a broken range still shows up. IFERROR hides every error, which is handy for reports but can mask mistakes. The generator offers both and shows the result of each in the live test.
When is the approximate match option safe to use in a VLOOKUP?: Only when the first column is sorted ascending and you want the closest value that does not exceed the lookup value, such as a score-to-grade table. The generator warns you, and the test grid flags unsorted data because the answer can then be wrong without any error.
Why do the XLOOKUP and INDEX MATCH versions appear below my VLOOKUP?: They return the same cell for the same inputs but avoid VLOOKUP's limits: they can look left and do not break when a column is inserted. Copy whichever matches the Excel or Google Sheets version your colleagues use.
Does the VLOOKUP Formula Generator send my table or formula anywhere?: No. The formula text and the sample cells are handled in your browser only. Your last settings and the cells of the test grid are remembered in local storage on your device so the page reopens as you left it.

Related tools

INDEX MATCH Formula Generator — Generate INDEX MATCH formulas for left lookups, two-way lookups and multiple criteria, with a live test table and copy-ready output.
Excel Formula Generator — Pick a task such as conditional sums, lookups, dates, text splitting or ranking and get a ready-to-paste Excel or Google Sheets formula.
Excel Formula Explainer — Paste any Excel or Google Sheets formula and get a plain-English, step-by-step breakdown of every function and argument.
Excel Data Cleaner — Clean messy spreadsheets: trim spaces, fix case, remove blanks and duplicates, standardise dates and numbers, then export .xlsx or CSV.
Excel to CSV — Convert an .xlsx or .xls spreadsheet to clean CSV or TSV, in your browser.
CSV to JSON — Convert CSV or TSV to JSON with type inference and nested keys, in your browser.

More Excel & Spreadsheets tools

INDEX MATCH Formula Generator — Generate INDEX MATCH formulas for left lookups, two-way lookups and multiple criteria, with a live test table and copy-ready output.
Excel Formula Explainer — Paste any Excel or Google Sheets formula and get a plain-English, step-by-step breakdown of every function and argument.
Excel Formula Generator — Pick a task such as conditional sums, lookups, dates, text splitting or ranking and get a ready-to-paste Excel or Google Sheets formula.
Excel Function Translator — Translate Excel formulas between English and German, French, Spanish, Italian, Portuguese, Dutch, Swedish and Russian, with correct separators.
Excel VBA Macro Generator — Generate ready-to-run VBA macros from a library of tasks (loops, delete blank rows, export sheets to PDF, send mail and more) with your own ranges and names.
CSV to Excel Converter — Turn CSV or TSV files into a formatted .xlsx workbook in your browser: delimiter detection, header styling, column types and multiple sheets.
JSON to Excel Converter — Convert JSON arrays or nested objects into an Excel workbook with flattened columns, one sheet per array and typed cells.
Excel Data Cleaner — Clean messy spreadsheets: trim spaces, fix case, remove blanks and duplicates, standardise dates and numbers, then export .xlsx or CSV.

See all Excel & Spreadsheets tools (9) · All tools