XLOOKUP practice exercises

XLOOKUP replaces VLOOKUP and INDEX/MATCH in Excel 365 and Google Sheets: separate lookup and return ranges, exact match by default and a built-in value for not found.

Start the first exercise

How XLOOKUP works

XLOOKUP looks up a value in one range and returns the value in the same position from another range. Unlike VLOOKUP, the two ranges are separate, so the answer column can sit to the left of the lookup column, and inserting a column never breaks the formula. It matches exactly by default.

=XLOOKUP(A2,$E$2:$E$50,$G$2:$G$50,"Not found") finds the ID in A2 in E2:E50 and returns the value next to it in G2:G50, or the text Not found when the ID isn't there.

XLOOKUP is in Excel 365, Excel 2021 and later and in Google Sheets. If you share files with people on older versions, learn VLOOKUP or INDEX and MATCH too. The exercises below cover a basic lookup, your own text when nothing is found, the last match instead of the first, the next larger value, and a lookup on two criteria.

  1. 1Look up a price with XLOOKUPIn F2, use XLOOKUP to return the Price for the SKU in E2: look for E2 in A2:A6 and return from C2:C6.
  2. 2XLOOKUP with a not-found valuePremiumP-109 is not in the list. In F2, use XLOOKUP to return the price for E2, and use its fourth argument to show "Not found" instead of #N/A.
  3. 3Find the latest match with XLOOKUPPremiumThe price list grows at the bottom each time a price changes. In F2, find the current price of the product in F1, the last match in the list. Search from the bottom with search_mode -1, the sixth argument: XLOOKUP(F1,B2:B8,C2:C8,,0,-1).
  4. 4The smallest box that fits with XLOOKUPPremiumIn C2, pick the smallest box that holds the volume in B2. If there is no exact match, XLOOKUP should take the next larger size: match_mode 1, the fifth argument, XLOOKUP(B2,$E$2:$E$5,$F$2:$F$5,,1).
  5. 5XLOOKUP with two criteriaPremiumIn F3, return the sales for the region in F1 and the product in F2. Look up 1 in the product of the two tests: XLOOKUP(1,(A2:A6=F1)*(B2:B6=F2),C2:C6).

Functions in this topic

Read the guide

Questions

Why does XLOOKUP show #NAME? in Excel?

XLOOKUP only exists in Excel 365, Excel 2021 and later. In Excel 2019, 2016 and older it shows #NAME?, and in a file saved by a newer version you may see _xlfn.XLOOKUP in the formula. Use VLOOKUP or INDEX and MATCH in files that must work on older versions.

How do I use XLOOKUP with multiple criteria?

Multiply the tests and look up 1: =XLOOKUP(1,(A2:A6=F1)*(B2:B6=F2),C2:C6). The product is 1 only on rows where both tests are true, so XLOOKUP finds the first row that matches both the region and the product. You can also join the columns with &, as in =XLOOKUP(F1&F2,A2:A6&B2:B6,C2:C6).

Can XLOOKUP return several columns at once?

Yes. Give it a return range with more than one column, such as =XLOOKUP(A2,E2:E50,F2:H50), and the matching row spills into the cells to the right. Make sure those cells are empty, or Excel shows an error instead of the result.