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.
- 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).
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.