XLOOKUP practice in Google Sheets
XLOOKUP came to Google Sheets in 2022 with the same arguments as in Excel: the value, the range to search, the range to return from, and an optional value for when nothing is found. It looks left as easily as right.
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.
- 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.
- 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.
- 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).
- 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).
- 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
Questions
Does Google Sheets have XLOOKUP?
Yes. Google Sheets added XLOOKUP in 2022, with the same arguments in the same order as Excel, so a formula you write in one works in the other.
How do I get the last match with XLOOKUP in Google Sheets?
Set search_mode, the sixth argument, to -1: =XLOOKUP(F1,B2:B8,C2:C8,,0,-1) searches from the bottom, so you get the last match instead of the first. The empty fourth argument keeps the default #N/A when nothing is found. The same formula works in Excel.