## Explanation

In this example, the goal is to look up a number with a certain amount of allowed tolerance, defined as **n**. In other words, with a given lookup number we are trying to find a number in a set of data that is ± **n**. In the worksheet shown, the number to find is in cell G4 and the number used for **n** is in G5. All data is in an Excel Table in the range B5:D15 named "data". This problem can be solved with the XLOOKUP function or with INDEX and MATCH together with Boolean logic. Both options are explained below.

### XLOOKUP function

The XLOOKUP function is a modern replacement for the VLOOKUP function. A key benefit of XLOOKUP is that it can handle array operations as the *lookup_array* or *return_array*. This means we can construct the *lookup_array* we need as part of the formula. We start off by providing *lookup_value* as 1:

```
=XLOOKUP(1,
```

*Note: The lookup value of 1 has nothing to do with the value for n, which is also 1 in this case. It is simply a common convention when using Boolean logic in lookup formulas. *

Next, we create the *lookup_array* with this expression:

```
--(ABS(data[Amount]-G4)<=G5)
```

Inside the ABS function, we subtract the value in cell G4 (5000) from all values in **data[Amount]**. Since we have 11 values in the **Amount** column, we get 11 results in an array like this:

```
{-3499.65;19350.86;-1;-3750.5;4099.75;6999.75;-700.15;1750.75;9999.9;-2899.15;5249.65}
```

Notice some values are negative. Next, the ABS function returns the absolute value of these values:

```
{3499.65;19350.86;1;3750.5;4099.75;6999.75;700.15;1750.75;9999.9;2899.15;5249.65}
```

Each value is then checked against the value for **n** in cell G5 (1). We are looking for values that are less than or equal to 1. You can see that the third number is in this range. The result is a new array that contains TRUE and FALSE values:

```
{FALSE;FALSE;TRUE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE}
```

In this array, a TRUE value indicates we have found a matching value that is within plus or minus n of our lookup value. Notice the third value is TRUE, while all others are FALSE. Next, because we are using 1 for *lookup_value*, we use a double-negative (--) to convert the TRUE and FALSE values to 1s and 0s:

```
{0;0;1;0;0;0;0;0;0;0;0}
```

Moving back to the XLOOKUP formula, we now have:

```
=XLOOKUP(1,{0;0;1;0;0;0;0;0;0;0;0},data)
```

Now it's clear why we are using 1 for *lookup_value*. XLOOKUP matches 1 and returns the third row of **data**.

### INDEX and MATCH option

This problem can also be solved with an INDEX and MATCH formula like this:

```
=INDEX(data,MATCH(1,--(ABS(data[Amount]-G4)<=G5),0),0)
```

The MATCH function is configured just like XLOOKUP above:

```
MATCH(1,--(ABS(data[Amount]-G4)<=G5),0)
```

The *lookup_value* is 1 and the *lookup_array* is created with the ABS function as above. After *lookup_array* is evaluated, we have:

```
MATCH(1,{0;0;1;0;0;0;0;0;0;0;0},0)
```

The MATCH function matches the third value (1), and returns 3 to the INDEX function as the *row_num*:

```
=INDEX(data,3,0)
```

Because *column_num* is set to zero, INDEX returns the entire third row from the data as a final result. In the current version of Excel, the result will spill into three cells, but in Legacy Excel, you will see only the first value. To display all values, you can either enter the formula as a multi-cell array formula, or use three separate formulas to return values for each of the columns like this:

```
=INDEX(data,MATCH(1,--(ABS(data[Amount]-G4)<=G5),0),1) // amount
=INDEX(data,MATCH(1,--(ABS(data[Amount]-G4)<=G5),0),2) // date
=INDEX(data,MATCH(1,--(ABS(data[Amount]-G4)<=G5),0),3) // client
```

Note the only difference in the formulas above is the column number.