← Google Sheets from Scratch: Formulas, QUERY, and Collaboration
Lesson
Lesson 2: XLOOKUP — the modern lookup
Use XLOOKUP as a simpler, more flexible lookup that searches in any direction, without a fragile column number.
XLOOKUP: lookups without the limits of VLOOKUP
XLOOKUP: lookups without the limits of VLOOKUP
XLOOKUP is the modern replacement for VLOOKUP in Google Sheets. Syntax: =XLOOKUP(search_key, lookup_range, result_range). Unlike VLOOKUP, the lookup column and the result column are set separately, so there’s no fragile numeric index and no “right only” limit.
For example, =XLOOKUP("Banana", A2:A10, C2:C10) finds “Banana” in column A and returns the matching value from column C — even if C is to the left of A, that’s not a problem. The optional fourth argument is a “not found” value: =XLOOKUP(E1, A2:A10, C2:C10, "No data") returns “No data” instead of the #N/A error.
Why is XLOOKUP easier than VLOOKUP? First, you don’t have to count columns — you just point to the result range directly. Second, you can look in any direction. Third, it handles “not found” on its own, without an IFERROR wrapper.
Still, VLOOKUP hasn’t gone anywhere: it lives on in millions of existing spreadsheets, and you will definitely run into it. Being able to read VLOOKUP matters as much as being able to write XLOOKUP.
Lesson notes
XLOOKUP: lookups without the limits of VLOOKUP
XLOOKUP is the modern replacement for VLOOKUP in Google Sheets. Syntax: =XLOOKUP(search_key, lookup_range, result_range). Unlike VLOOKUP, the lookup column and the result column are set separately, so there’s no fragile numeric index and no “right only” limit.
For example, =XLOOKUP("Banana", A2:A10, C2:C10) finds “Banana” in column A and returns the matching value from column C — even if C is to the left of A, that’s not a problem. The optional fourth argument is a “not found” value: =XLOOKUP(E1, A2:A10, C2:C10, "No data") returns “No data” instead of the #N/A error.
Why is XLOOKUP easier than VLOOKUP? First, you don’t have to count columns — you just point to the result range directly. Second, you can look in any direction. Third, it handles “not found” on its own, without an IFERROR wrapper.
Still, VLOOKUP hasn’t gone anywhere: it lives on in millions of existing spreadsheets, and you will definitely run into it. Being able to read VLOOKUP matters as much as being able to write XLOOKUP.