# How to find the first value within a tolerance of a target in Excel

Test the distance from the target against the tolerance: `=XLOOKUP(TRUE,ABS(B2:B8-E1)<=G1,A2:A8,"No match")`.

**Excel:** `=XLOOKUP(TRUE,ABS(B2:B8-E1)<=G1,A2:A8,"No match")` (Excel 2021, 2024 and Microsoft 365)

**Google Sheets:** the same formula works.

## Example

Sample data (row 1 is the header):

| A | B | C | D | E | F | G |
| --- | --- | --- | --- | --- | --- | --- |
| Invoice | Amount |  | Payment | 1249.5 | Tolerance | 1 |
| INV-3301 | 980.4 |  | Matching invoice |  |  |  |
| INV-3302 | 1475 |  |  |  |  |  |
| INV-3303 | 1250.25 |  |  |  |  |  |
| INV-3304 | 615.8 |  |  |  |  |  |
| INV-3305 | 1248.1 |  |  |  |  |  |
| INV-3306 | 2210 |  |  |  |  |  |
| INV-3307 | 1250 |  |  |  |  |  |

Formula in E2: `=XLOOKUP(TRUE,ABS(B2:B8-E1)<=G1,A2:A8,"No match")`

Result: INV-3303

## How it works

`ABS(B2:B8-E1)` is how far each invoice is from the payment, ignoring direction. Comparing it with the tolerance gives TRUE for every invoice within $1, and XLOOKUP returns the first: INV-3303, $0.75 away. INV-3305 is $1.40 away and doesn't qualify.

## Other ways

- `=INDEX(A2:A8,MATCH(TRUE,INDEX(ABS(B2:B8-E1)<=G1,0),0))`: Every Excel version, without Ctrl+Shift+Enter.
- `=FILTER(A2:A8,ABS(B2:B8-E1)<=G1)`: Lists every candidate so you can choose. (Both INV-3303 and INV-3307.)

## Common mistakes

- This returns the first invoice within tolerance, not the closest. INV-3307 is closer here. Use the closest-value formula with the tolerance as a check if that matters.
- Without ABS, invoices below the payment always pass, because their difference is negative.

## Questions

**How do I get the closest invoice within the tolerance?**

Look up the smallest distance, then check it: `=LET(d,ABS(B2:B8-E1),IF(MIN(d)<=G1,XLOOKUP(MIN(d),d,A2:A8),"No match"))`.

**How do I use a percentage tolerance?**

Compare with a share of the payment: `ABS(B2:B8-E1)<=E1*1%`.

Every formula and result on this page was checked by running it in the Spreadsheet Practice formula engine on 2026-10-30.

Source: https://spreadsheetpractice.com/formulas/value-within-tolerance