XLOOKUP horizontal lookup

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.

=XLOOKUP(J4,sales_amount,reduction,,-1)

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:

=XLOOKUP(I4,sales_amount,reduction,,-1)

Without named ranges the formula looks like:

=XLOOKUP(I4,$D$3:$G$3,$D$4:$G$4,,-1)

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.

hlookup horizontal lookup
=HLOOKUP(I4,D3:G4,2,TRUE)
=HLOOKUP(I4,D3:G4,2,FALSE)

Differences between XLOOKUP and HLOOKUP

It is worthy to note that significant differences exist between the two lookup functions.

HLOOKUP

  • 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

XLOOKUP

  • 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.

Stay tuned.

Related formulas and resources

Categories Formula