Lookup formula builder for Excel and Google Sheets
Build an XLOOKUP, VLOOKUP or INDEX/MATCH formula step by step and see what each part does. It runs in your browser, nothing is uploaded.
For example A2, or Orders!B2.
Where the lookup value lives, such as A:A or Sheet1!$A$2:$A$100.
Same number of rows as the search column.
The first column is the one searched.
1 = first column of the table.
Your formula
What each part does
The same lookup in the other functions
How to use it
- Choose a function and say what you are looking for. A cell like
A2is the most common choice. - Enter the column to search and the column to return. For VLOOKUP, enter one table range and a column number.
- Copy the formula. The same lookup is shown in the other two functions, so you can switch if your Excel version needs it.
XLOOKUP vs VLOOKUP vs INDEX/MATCH
All three do the same job: find a value in one column and return something from the same row of another column. They differ in how much you have to count and where they work.
| Function | Looks left | Works in | Watch out for |
|---|---|---|---|
| XLOOKUP | Yes | Excel 2021 and 365, Google Sheets | Missing in Excel 2019 and older |
| VLOOKUP | No | Every version | Column number breaks if you insert a column |
| INDEX/MATCH | Yes | Every version | Longer to write and read |
Exact or approximate match
Use exact match for IDs, SKUs, emails and names. Approximate match is for ranges, like finding a tax bracket or a discount tier from an order total. It returns the largest value that does not exceed yours, and VLOOKUP and MATCH need the first column sorted from smallest to largest, otherwise the result is silently wrong.
Handling values that are not found
When nothing matches you get #N/A. That is useful while you build, but it spreads into totals and charts. XLOOKUP has a built-in argument for the "not found" text. With VLOOKUP and INDEX/MATCH, wrap the formula in IFNA, which only catches #N/A and therefore does not hide other mistakes the way IFERROR does.
Common mistakes this builder prevents
- A VLOOKUP column number of 0 or one bigger than the table.
- Search and return ranges with different lengths, which gives #VALUE!.
- Missing
$signs, so the ranges slide down when you copy the formula. - Text that needs quotes, written as a bare word.
Once a lookup works, the next problems are usually messy source data and formulas that nobody dares to touch. Those are worth fixing properly, once.
Questions people ask
Which lookup function should I use?
Use XLOOKUP if you have Excel 2021, Microsoft 365 or Google Sheets. It is the simplest and can look left. Use INDEX/MATCH for older Excel versions. VLOOKUP still works everywhere but only returns columns to the right of the one it searches.
Why does my VLOOKUP return #N/A?
Usually the value is not in the first column of the table, the lookup and table values differ by a hidden space or by text versus number, or you used approximate match on unsorted data. Trim the data and use exact match (FALSE).
Why does the formula show semicolons?
Excel and Sheets in many regions (for example Germany, France, Brazil and Indonesia) use semicolons between arguments because the comma is the decimal mark. Switch the separator option to match your settings.
Can the formula look to the left of the search column?
Yes, with XLOOKUP or INDEX/MATCH. VLOOKUP cannot, which is why the tool tells you when a VLOOKUP version is not possible and suggests the other two.
Lookups still not behaving, or a sheet that needs a rebuild? We fix broken formulas and build dashboards at a fixed price, in 2–4 days.
See spreadsheet help