In this formula, the if-not-found argument is used to perform a more specific search:
search-value: 13, the value to search for in the range specified in search-range.
search-range: Length, the column to search in.
return-range: Width, the column containing the value to return if a match for search-value is found.
if-not-found: "No match", the string to display if a product with a length of 13 cm is not found.
match-type: exact match (0). This searches only for a length of 13 cm.
Find the next closest value
XLOOKUP can also provide a broad search based on a specific value and values close to it. If you change match-type from the formula above, you can return the width that matches a length of 13 cm, or the next smallest value. The formula below returns a width of 11 cm:
In this example, the first XLOOKUP searches for the price of Product 1, and the second XLOOKUP searches for the price of Product 3. The colon (:) between the XLOOKUP functions indicates that SUM should return not just the total price of Product 1 and Product 3, but also any values in between.
In the formula below, XLOOKUP is used with XMATCH