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.