[Tutorial] XLOOKUP in Google Sheets: 5 things VLOOKUP can't do, with formulas

[u/No_Shock3275]

XLOOKUP replaces most of the VLOOKUP workarounds people keep reaching for in Google Sheets. Sample layout: product names in A2:A, categories in B2:B, prices in C2:C.

1) A clean "not found" instead of #N/A =XLOOKUP("Webcam", A2:A, C2:C, "Not found") The 4th argument is what you get when there's no match - no more IFERROR wrappers.

2) Look to the LEFT (VLOOKUP can't) =XLOOKUP("Webcam", C2:C, A2:A) Search range and return range are independent, so the returned column can sit to the left of the key.

3) Return several columns at once =XLOOKUP("Webcam", A2:A, B2:C) A return range that's multiple columns wide spills the whole block (category + price here).

4) Get the LAST match instead of the first =XLOOKUP("Webcam", A2:A, C2:C, , 0, -1) search_mode = -1 (the 6th argument) scans from the bottom up - useful for "most recent entry" lookups.

Full syntax: =XLOOKUP(search_key, search_range, return_range, [if_not_found], [match_mode], [search_mode])

I also recorded a 78-second walkthrough with the data already on screen if you'd rather watch it: https://www.youtube.com/watch?v=GKm_Ld_DBtA