Lesson 4: XLOOKUP, the modern way

SheetDrill Lesson 4 · XLOOKUP: the modern wayFR

Lesson 4 · Pro

fx

✓

Lesson 4 done.

You can look up in any direction, handle missing items without IFERROR, and plug a lookup into IF, math or MAX. In newer Excel and Google Sheets, XLOOKUP replaces VLOOKUP for almost everything.

Lesson 5: SUMIFS and COUNTIFS →

What you're learning

XLOOKUP takes three things: what to find, where to look, and what to return: =XLOOKUP(G2, A2:A7, D2:D7). The two ranges are separate columns, so the return column can be anywhere, even to the left. No column numbers to count, and it's an exact match by default.

Add a fourth argument for when nothing is found: =XLOOKUP(G2, A2:A7, B2:B7, "Not found"). The lookup and return ranges must cover the same rows, or you get #VALUE!. XLOOKUP is in Excel 2021, Microsoft 365 and Google Sheets; on older Excel use INDEX/MATCH.