Google Sheets XLOOKUP / VLOOKUP Formula Builder
Pick your lookup value, where to search and what to pull back — this builder writes the exact XLOOKUP, VLOOKUP or INDEX/MATCH formula, including exact-match and if-not-found handling.
=XLOOKUP(E1, A:A, B:B)
How this lookup builder works
XLOOKUP is the modern choice: XLOOKUP(search_key, lookup_range, result_range, [missing], [match_mode]). You give it the column to search and the column to return separately, so it works left or right and never breaks when you insert a column. It's exact-match by default, and you can hand it a value to show when nothing matches instead of an #N/A.
VLOOKUP is older and more fragile: VLOOKUP(search_key, range, index, is_sorted). The return column is a number counted from the left edge of the range, and it can only look to the right of the key. Always pass FALSE as the last argument for an exact match — leaving it TRUE silently returns approximate (and often wrong) results.
INDEX + MATCH does what XLOOKUP does but works in any spreadsheet version: INDEX(result_range, MATCH(search_key, lookup_range, 0)). The 0 forces an exact match. Wrap any of these in IFERROR(..., "Not found") to replace the #N/A with your own text — the builder does this for you when you fill the "if not found" box.
Common ready-to-paste examples
XLOOKUP a name in E1 against column A, return column B:
=XLOOKUP(E1, A:A, B:B)
XLOOKUP with a friendly message when nothing matches:
=XLOOKUP(E1, A:A, B:B, "Not found")
VLOOKUP, exact match, return the 2nd column of A:C:
=VLOOKUP(E1, A:C, 2, FALSE)
INDEX/MATCH equivalent (works in older sheets, looks left too):
=INDEX(B:B, MATCH(E1, A:A, 0))
VLOOKUP wrapped so a miss shows blank, not #N/A:
=IFERROR(VLOOKUP(E1, A:C, 2, FALSE), "")
FAQ
Should I use XLOOKUP or VLOOKUP in Google Sheets?
Use XLOOKUP if it's available — it searches and returns separate columns, works in both directions, defaults to exact match, and has a built-in 'if not found' value. VLOOKUP is fine for simple left-to-right lookups but breaks when you insert columns and needs a manual column number.
Why does my VLOOKUP return the wrong value or #N/A?
The two usual causes: you left the last argument as TRUE (approximate match) instead of FALSE (exact), or the lookup column isn't the first column of your range. VLOOKUP can only look to the right and counts the return column from the left edge of the range you give it.
How do I show a custom message instead of #N/A when nothing matches?
With XLOOKUP, put the text in the 4th argument: =XLOOKUP(E1, A:A, B:B, "Not found"). With VLOOKUP or INDEX/MATCH, wrap the whole formula in IFERROR: =IFERROR(VLOOKUP(E1, A:C, 2, FALSE), "Not found"). The builder adds this when you fill the 'if not found' box.
What's the advantage of INDEX/MATCH over VLOOKUP?
INDEX/MATCH can return a column to the left of the search column, doesn't break when you insert or delete columns (it references ranges, not a fixed number), and is often faster on large sheets. The trade-off is it's two functions nested instead of one.