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