Learn how to **find the closest match** in numeric data fast using the XLOOKUP function or INDEX and MATCH combinations.

**Finding the closest match in Excel** is a real challenge! Everyone knows that the **XLOOKUP function** is a swiss knife, so we’ll use the 5th argument (match mode) in the example. In the second part of the guide, we’ll combine the main function with the **ABS** function to get the closest match. The million-dollar question: is the “next larger item” or the “next smaller item” the closest? **Not at all!**

## How to find the closest match in Excel

Here is our table, which contains two columns, **Location**, and **Price**. For the sake of simplicity, we use **named ranges**.

- Location:
**B3:B12** - Price:
**C3:C12**

### Find the next smaller item (or exact match)

In the first example, we try to find the closest match and set the 5th argument to -1.

The arguments look like this:

**lookup value**: F4**lookup array**: Price**return array**: Location**match mode:**-1

**Formula:**

**=XLOOKUP(F4, Price, Location,,-1)**

**Tip:** If you need to skip one or more optional argument(s), type a comma between the arguments; the function will not use it. We do not use the “if not found” and “search_mode” arguments in the example.

The result is Rome. Okay, take a look at how the formula works. First, Excel evaluates the formula and uses the lookup value ($1659) to find an exact match. If an exact match is not found, it returns with the next smaller item, in this case, $1600. Finally, the formula returns with the corresponding record, Rome, in the example.

### Find the next larger item (or exact match)

In the following example, our goal is the same, but we’ll try to find the closest match using another value for match mode.

Evaluate the formula:

=XLOOKUP(F4, Price, Location,,1)

The formula returns with “**Sydney**” if you use the F4 cell as a lookup value and set the match mode argument to 1. If an exact match is not found, it returns with the next larger item, in this case, $1700. Finally, the formula gets the corresponding value in the **Location** column, **Sydney**.

We had different results in case of no exact match was found.

### Solve the closest match challenge: XLOOKUP and ABS function

**Now, here comes the moment of truth!** If you want to find the closest value, you must use the ABS function.

Please look at **column J**! First, let us see the formula, then we’ll explain how it works.

**=XLOOKUP(0, ABS(Price – lookup_value) ,Location,,1)**

Evaluate the formula in **J4** from the inside out:

**=XLOOKUP(0, ABS(Price-G4), Location,,1)**

**lookup value = 0****lookup array**= ABS(Price – lookup_value) – it create a new dynamic array with calculated values**return array**= Location**match mode**= 1

**The lookup value is 0**, so the formula tries to find the closest value to 0 in the newly created temporary array in column E. The closest value is $41, and the corresponding value in the same row is **Sydney**.

**The formula in cell I5 works the same; the** result is **Los Angeles.**

Finally, evaluate the formula in **I6** from the inside out:

Finally, here is a quick summary! The demonstrated method finds the closest value without any trouble.

### Workaround with INDEX and MATCH

Here is a workaround with the **INDEX and MATCH functions**. The task is the same. Again, try to find the closest match if you have a recent Excel version.

Formula:

**=INDEX(Location, MATCH(MIN(ABS(Price-F4)), ABS(Price-F4), 0))**

**MIN**function finds the smallest differences**ABS**function converts the negative value to positive**MATCH**returns the position in the array Price**INDEX**returns the correspondent value in the Location array