=XLOOKUP(value,rng1,rng2,,-1)// approximate match=XLOOKUP(value,rng1,rng2)// exact match
To perform a horizontal lookup with the XLOOKUP function, supply a lookup value, a lookup array, and a result array. Provide a match_mode argument for approximate match if needed. In the example shown, the formula in I5, copied down the table, is:
where quantity (C4:F4) and discount (C5:F5) are named ranges.
One nice benefit of the XLOOKUP function is that the syntax used for horizontal lookups is the same as for vertical lookups.
In the example shown, the data in C4:F5 contains quantity-based discounts. As the quantity increases, the discount also increases. The table to the right shows the discount returned by XLOOKUP for several random quantities. The formula in F5, copied down, is:
The lookup_array is the named range quantity (C4:F4)
The return_array is the named range discount (C5:F5)
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)
At each row, XLOOKUP looks up the quantity in the range C4:F4. When an exact match is found, the corresponding discount in row 5 is returned. When an exact match is not found, the discount associated with the next smallest quantity is returned.
XLOOKUP vs HLOOKUP
The equivalent HLOOKUP formula for this example, is either of the two formulas below:
One of the nice advantages of XLOOKUP over VLOOKUP is that XLOOKUP can work with arrays directly, instead of requiring ranges on a worksheet. This makes it possible to assemble arrays in the formula, and push these into the function. Working one...
XLOOKUP can handle arrays natively, which makes it a very useful function when constructing criteria based on multiple logical expressions. In the example shown, we are looking for the order number of the first order to Chicago over $250. We are...
Whereas VLOOKUP is limited to lookups to the right of the lookup column, XLOOKUP can lookup values to the left natively. This means XLOOKUP can be used instead of INDEX and MATCH to find values to the left in a table or range. In the example shown,...
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...
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...
The Excel XLOOKUP function is a modern and flexible replacement for older functions like VLOOKUP, HLOOKUP, and LOOKUP. XLOOKUP supports approximate and exact matching, wildcards (* ?) for partial matches, and lookups in vertical or horizontal...
Excel Formula Training
Formulas are the key to getting things done in Excel. In this accelerated training, you'll learn how to use formulas to manipulate text, work with dates and times, lookup values with VLOOKUP and INDEX & MATCH, count and sum with criteria, dynamically rank values, and create dynamic ranges. You'll also learn how to troubleshoot, trace errors, and fix problems. Instant access. See details here.