Tell the generator which column to search, what to look for and which column you want back, and it writes the lookup formula for Google Sheets. It handles the details that usually break a lookup: ARRAYFORMULA around XLOOKUP with several conditions, the column number in VLOOKUP, the last match instead of the first, and ranges that start and end on the same rows. Nothing you type leaves your browser.
Your lookup formula
Examples the generator builds
We pasted each of these into Google Sheets on a small table (region in A, product in B, month in C, units in D, price in E, rows 2 to 9) and got the result shown.
| You want | Formula | Result |
|---|---|---|
| Units for one product | =XLOOKUP("Cherry", B2:B9, D2:D9) |
3 |
| A column to the left | =XLOOKUP("Cherry", B2:B9, A2:A9) |
East |
| Two conditions | =ARRAYFORMULA(XLOOKUP(1, (A2:A9="North")*(B2:B9="Apple"), D2:D9)) |
10 |
| Three conditions | =ARRAYFORMULA(XLOOKUP(1, (A2:A9="North")*(B2:B9="Apple")*(C2:C9="Feb"), D2:D9)) |
12 |
| The last match | =ARRAYFORMULA(XLOOKUP(1, (A2:A9="North")*(B2:B9="Apple"), D2:D9,, 0, -1)) |
15 |
| Your own text when nothing matches | =ARRAYFORMULA(XLOOKUP(1, (A2:A9="West")*(B2:B9="Apple"), D2:D9, "Not found")) |
Not found |
| Values from cells G1 and G2 | =ARRAYFORMULA(XLOOKUP(1, (A2:A9=G1)*(B2:B9=G2), D2:D9)) |
10 |
| Three adjacent columns | =ARRAYFORMULA(XLOOKUP(1, (A2:A9="North")*(B2:B9="Apple"), C2:E9)) |
Jan, 10, 1.2 |
| Columns in another order | =XLOOKUP("Cherry", B2:B9, HSTACK(E2:E9, C2:C9)) |
3, Feb |
| INDEX MATCH with two conditions | =ARRAYFORMULA(INDEX(D2:D9, MATCH(1, (A2:A9="North")*(B2:B9="Apple"), 0))) |
10 |
| VLOOKUP with a fallback | =IFNA(VLOOKUP("Fig", B2:E9, 4, FALSE), "-") |
– |
| Every match | =FILTER(D2:D9, A2:A9="North", B2:B9="Apple") |
10, 12, 15 (down) |
| Every match, to the end of the sheet | =FILTER(B2:D, A2:A="North", B2:B="Apple") |
3 rows, 3 columns |
The formulas with a semicolon separator, for spreadsheets set to a European or Latin American locale, follow the same rules as our separator converter; we tested the comma versions shown above.
Mistakes the generator avoids
We also ran the versions that go wrong. Some show an error; the worst ones return a believable value from the wrong row.
- XLOOKUP with several conditions and no ARRAYFORMULA. It returned #VALUE! or Array arguments to XLOOKUP are of different size. in every cell we tried. The generator always adds ARRAYFORMULA in that case.
- OR conditions added together. A row where both conditions are true adds up to 2, so XLOOKUP skipped it and returned the next row, with no error.
- A VLOOKUP column number that no longer fits. After we inserted a column, VLOOKUP returned the month instead of the units. The generator shows this warning whenever you choose VLOOKUP.
- VLOOKUP returning a column to the left. It can’t, so the generator suggests XLOOKUP or INDEX MATCH instead of building a formula that returns #N/A.
- Ranges of different lengths. Every range in a generated formula starts and ends on the same rows. In our test, a range one row short silently skipped the last row.
The full results are in VLOOKUP vs XLOOKUP vs INDEX MATCH: 10 tests.