# 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(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.