Explanation
In the example shown, the table in B4:C13 contains quantity-based discounts. As the quantity increases, the discount also increases. The table in E4:F10 shows the discount returned by XLOOKUP for several random quantities. XLOOKUP is configured to use the quantity in column E to find the appropriate discount. The formula in F5, copied down, is:
=XLOOKUP(E5,qty,disc,-1)
- The lookup_value comes from cell E5
- The lookup_array is the named range qty (B5:B13)
- The return_array is the named range disc (C5:C13)
- The not_found argument is not provided
- The match_mode is set to -1 (exact match or next smaller)
- The search_mode is not provided and defaults to 1 (first to last)
Note: Be aware if you supply an empty string ("") for not_found. If no match is found, XLOOKUP will display nothing instead of #N/A. In other words, an empty string doesn’t mean NULL, it means “display nothing if no match is found”.
At each row, XLOOKUP looks up the quantity in column E in the range B5:B13. When an exact match is found, the corresponding discount in column C is returned. When an exact match is not found, the discount associated with the next smallest quantity is returned.
XLOOKUP vs VLOOKUP
The equivalent VLOOKUP formula for this example is either of the two formulas below:
=VLOOKUP(E5,B5:C13,2) // default approximate
=VLOOKUP(E5,B5:C13,2,1) // explicit approximate
There are several notable differences:
- VLOOKUP requires the full table array as the second argument. XLOOKUP requires only the range with lookup values.
- VLOOKUP requires a column index to specify a result column. XLOOKUP requires a range that contains result values.
- VLOOKUP performs an approximate match by default . XLOOKUP performs an exact match by default.
- VLOOKUP requires lookup data to be sorted by lookup value. XLOOKUP works with unsorted data.
Dynamic Array Formulas are available in Office 365 only.
Explanation
In the example shown, cell G4 contains the lookup value, “Berlin”. XLOOKUP is configured to find this value in the table, and return the population. The formula in G5 is:
=XLOOKUP(G4,B5:B18,D5:D18) // get population
- The lookup_value comes from cell G4
- The lookup_array is the range B5:B18, which contains City names
- The return_array is D5:D18, which contains Population
- The match_mode is not provided and defaults to 0 (exact match)
- The search_mode is not provided and defaults to 1 (first to last)
To return County instead of population, only the return array is changed. The formula in G6 is:
=XLOOKUP(G4,B5:B18,C5:C18) // get country
XLOOKUP vs VLOOKUP
The equivalent VLOOKUP formula to retrieve population is:
=VLOOKUP(G4,B5:D18,3,0)
There are a few notable differences which make XLOOKUP more flexible and predictable:
- VLOOKUP requires the full table array as the second argument. XLOOKUP requires only the range with lookup values.
- VLOOKUP requires a column index argument to specify a result column. XLOOKUP requires a range.
- VLOOKUP performs an approximate match by default . This behavior can cause serious problems . XLOOKUP performs an exact match by default.
Dynamic Array Formulas are available in Office 365 only.
