Abstract
Transcript
In this video, well set up theXLOOKUP functionto perform an approximate match.
In this worksheet, the table in B5:C9 contains quantity-based discounts.
As the quantity increases, the discount also increases.
I’ll name B5:B9quantityand I’ll name C5:C9discount.
This is an optional step, but it will eliminate the need to useabsolute referencesinside XLOOKUP.
AnExcel Tablewould also work well in this case.
Next, I’ll put the cursor in F5.
After typing an equals sign and the letters “xl” I’ll press TAB to complete.
The first argument for XLOOKUP islookup_value.
We want to pick up this value from cell E5.
The next argument islookup_array.
For this, we want to use the named rangequantityin column B.
Next, we havereturn_array.
If I stop here and copy the formula down, we get mostly #NA errors.
This is because XLOOKUP performs an exact match by default.
The other values do not appear in the lookup array.
The next argument is thenot_foundmessage.
Next, we havematch_mode.This is the argument we need for approximate match.
We don’t need to providesearch_mode, since XLOOKUP will start at the top by default.
Now when I enter the formula, you’re able to see we have more sensible results.
For each quantity, we get the correct discount.
As before, 100 is an exact match and returns 25%.
