FormulasFree · No signup

VLOOKUP & XLOOKUP Generator

Stop guessing column numbers. Enter what you are looking for and where the table lives, and get a lookup formula that returns the right column — in VLOOKUP, XLOOKUP or INDEX MATCH form.

Runs in your browserUpdated October 25, 2026Exact match by default · VLOOKUP, XLOOKUP and INDEX MATCH

The short answer

A VLOOKUP generator builds a lookup formula from three inputs: the value to find, the table range and the column to return. It adds exact-match mode and locked references automatically, and can output XLOOKUP or INDEX MATCH instead when you need to look left or avoid column-count errors.

Value to findA cell reference such as A2, or a literal value such as EMP-104.Table rangeInclude the lookup column and every column you might return.Column number to returnColumn 3 = CValue if no matchUsed by XLOOKUP and by the IFERROR wrapper.

Formula style

Lock ranges with $

Column	Role	Excel letter
1	Lookup column (must contain the value)	A
2	Available	B
3	Returned by this formula	C
4	Available	D

=XLOOKUP(A2,$A$2:$A$500,$C$2:$C$500,"Not found",0)

XLOOKUP needs Microsoft 365 or Excel 2021. It can return a column to the left of the lookup column.

Searches A2:A500 for A2 and returns the value from C2:C500. The fourth argument is shown when nothing matches; the final 0 forces an exact match.

Still returning #N/A? The lookup value and the table's first column may be different data types.

Check the formula

How to use it

1Enter the cell or value you want to find, e.g. A2 or "EMP-104".
2Enter the full table range, including the column that contains the lookup value.
3Choose the column number to return — the generator shows the matching column letter.
4Pick VLOOKUP, XLOOKUP or INDEX MATCH, then copy the formula.

Questions about the vlookup generator

Why does my VLOOKUP return the wrong value?

The two most common reasons are a missing fourth argument (so Excel uses approximate match) and a column number that is wrong after inserting a column. The generator adds FALSE for exact match and locked ranges; XLOOKUP avoids the column-number problem entirely.

Should I use VLOOKUP, XLOOKUP or INDEX MATCH?

Use XLOOKUP if you have Microsoft 365 or Excel 2021 — it looks in both directions and has a built-in not-found value. Use INDEX MATCH for older versions when the return column is left of the lookup column. Use VLOOKUP only when the table is small, static and the return column is to the right.

Can the generator look up text with wildcards?

VLOOKUP and XLOOKUP support wildcards in exact-match mode. Enter the lookup value with * or ? and keep exact match selected. For complex pattern matching, use XLOOKUP with match mode 2.

Does it handle sheet names with spaces?

Yes. Enter the sheet name in the optional field; names containing spaces are wrapped in apostrophes automatically, e.g. 'Q1 Data'!$A$2:$D$500.

Need it done for you?

Lookup returning #N/A on your assignment?

Send the sheet. We fix the lookup, explain why it failed, and make sure it survives a copy-down before your deadline.

Get a fixed quote Ask on WhatsApp See pricing →

From $15/hr
Fixed quote before work starts
Free minor revisions
Human experts, not AI files

Related free tools

All tools →

Formula ExplainerPaste any Excel formula and get a plain-English explanation of every function, range and nested step.Open tool →Formula CheckerCheck any Excel formula for syntax errors, wrong argument counts, approximate-match traps and #REF! errors.Open tool →Formula GeneratorBuild accurate Excel formulas from plain choices: SUMIF, SUMIFS, COUNTIF, IF, IFERROR, TEXTJOIN, INDEX MATCH and more.Open tool →

Keep reading

Column

Role

Excel letter

Lookup column (must contain the value)

Available

Returned by this formula