Learn how to apply a horizontal lookup using the XLOOKUP function. We’ll add a lookup value, a lookup array, and a result array. If you want to calculate an approximate match, use the match_mode argument.
Compared to HLOOKUP, XLOOKUP has a more logical structure. Late, we’ll show why we think it’s important for all Excel users.
Generic formula for horizontal lookup
=XLOOKUP(value,range1,range2,,-1) // approximate match =XLOOKUP(value,range1,range2) // exact match
Let us see the example!
Create a named range from D3:G3 as category and D4:G4 as a price reduction.
Enter the formula in cell J4 and copy down the entire range.
How the lookup formula works
XLOOKUP is suitable for a lot of different situations because we can use the same syntax for vertical and horizontal lookups.
In our example, the initial data set in D3:G4 range contains category-based reductions. In this case, the sales amount increases the reduction also increases.
The result table shows the actual discount returns by XLOOKUP.
Enter the formula in cell J4:
Without named ranges the formula looks like:
Setting up the arguments
Now assign the arguments to cell values. Configure the arguments in the following way:
- lookup_value gets from cell I4
- lookup_array is the category, D3:G3 (it comes from a named range)
- return_array is the named range reduction, D4:G4
- not_found argument is not necessary in this case
- match_mode is -1; we find the exact match or next smaller value
- search_mode: leave the argument empty (the default value is 1)
As first, XLOOKUP searches the category in range, D3:G3. After that – if we found an exact match – the corresponding value will be returned. In the example, it’s 10%.
In the example, the exact match is not found in the given range. XLOOKUP will use the next smallest value as a result.
Horizontal lookup using HLOOKUP
If you want to apply HLOOKUP formula evaluate the formulas:
- Apply TRUE for an approximate match
- FALSE to find exact match
Now let’s repeat the above-mentioned example using HLOOKUP:
In the first case – if we search an approximate match – we are using TRUE, the result is the same as the XLOOKUP.
If we add FALSE as the last parameter of HLOOKUP, the result is an error, #N/A.
Differences between XLOOKUP and HLOOKUP
It is worthy to note that significant differences exist between the two lookup functions.
- we have to add the full table array as the second argument
- adding row index to specify a result column is necessary
- get an approximate match by default.
- work with sorted data
- performs horizontal lookups only
- requires only the range with lookup values.
- requires a range that contains result values.
- performs an exact match by default.
- performs both horizontal and vertical lookups
- work with unsorted data
We strongly recommend you using XLOOKUP for horizontal lookups. It’s a simple and elegant way the get the proper result.
As a general conclusion: XLOOKUP is a more user-friendly function as HLOOKUP and just makes life easier.