Google Sheets Lookup Formula Generator

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.

1. Your data

2. Find the row where…

And (optional)

And also (optional)

3. What to return

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.

    Related guides