## Explanation

At the core, this is an INDEX and MATCH formula: MATCH locates the position of the closest match, feeds the position to INDEX, and INDEX returns the value at that position in the Trip column. The hard work is done with the MATCH function, which is carefully configured to match the "minimum difference" like this:

```
MATCH(MIN(ABS(cost-E5)),ABS(cost-E5),0)
```

Taking things step-by-step, the lookup value is calculated with MIN and ABS like this:

```
MIN(ABS(cost-E5)
```

First, the value in E5 is subtracted from the named range **cost** (C5:C14). This is an array operation, and since there are 10 values in the range, the result is an array with 10 values like this:

```
{899;199;250;-201;495;1000;450;-101;500;795}
```

These numbers represent the difference between each cost in C5:C15 and the cost in cell E5, 700. Some values are negative because a cost is lower than the number in E5. To convert negative values to positive values, we use the ABS function:` `

```
ABS({899;199;250;-201;495;1000;450;-101;500;795})
```

which returns:

```
{899;199;250;201;495;1000;450;101;500;795}
```

We are looking for the *closest match*, so we use the MIN function to find the *smallest difference*, which is 101:

```
MIN({899;199;250;201;495;1000;450;101;500;795}) // returns 101
```

This becomes the lookup value inside MATCH. The lookup array is generated as before:

```
ABS(cost-E5) // generate lookup array
```

which returns the same array we saw earlier:

```
{899;199;250;201;495;1000;450;101;500;795}
```

We now have what we need to find the position of the closest match (smallest difference), and we can rewrite the MATCH portion of the formula like this:

```
MATCH(101,{899;199;250;201;495;1000;450;101;500;795},0) // returns 8
```

With 101 as the lookup value, MATCH returns 8, since 101 is in the 8th position in the array. Finally, this position is fed into INDEX as the row argument, with the named range **trip** as the array:

```
=INDEX(trip,8)
```

and INDEX returns the 8th trip in the range, "Spain". When the formula is copied down to cells F6 and F7, it finds the closest match to 1000 and 1500, "France" and "Thailand" as shown.

Note: if there is a tie, this formula will return the *first* match.

### With XLOOKUP

The XLOOKUP function provides an interesting way to solve this problem, because a match type of 1 (exact match or next largest) or -1 (exact match or next smallest) doesn't require data to be sorted. This means we can write a formula like this:

```
=XLOOKUP(0,ABS(cost-E5),trip,,1)
```

As above, we use the absolute value of (cost-E5) to create a lookup array:

```
{899;199;250;201;495;1000;450;101;500;795}
```

Then we configure XLOOKUP to look for zero, with match type set to 1, for exact match or next largest. We supply the named range **trip** as the return array, so the result is "Spain" as before.