How to find the first value within a tolerance of a target in Excel
Formula (Excel and Google Sheets)
=XLOOKUP(TRUE,ABS(B2:B8-E1)<=G1,A2:A8,"No match")Test the distance from the target against the tolerance: =XLOOKUP(TRUE,ABS(B2:B8-E1)<=G1,A2:A8,"No match").
- Result on the example
- INV-3303 in
E2 - Works in
- Excel 2021, 2024 and Microsoft 365
- Google Sheets
- Same formula
See it work
The formula is already in E2. Change any input and the result updates, or edit the formula to experiment.
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.
=XLOOKUP(TRUE,ABS(B2:B8-E1)<=G1,A2:A8,"No match")Result"INV-3303"
B2:B87 rows × 1 col: $980.40, $1,475.00, $1,250.25, $615.80, …A range: 7 cells in one column.
E1$1,249.50The value in E1.
B2:B8-E1{7 rows × 1 col} -269.1, 225.5, 0.75, -633.7, …The - operator subtracts.
ABS(B2:B8-E1){7 rows × 1 col} 269.1, 225.5, 0.75, 633.7, …ABS: Absolute value.- number
B2:B8-E1{7 rows × 1 col} -269.1, 225.5, 0.75, -633.7, …
- number
G1$1.00The value in G1.
ABS(B2:B8-E1)<=G1{7 rows × 1 col} FALSE, FALSE, TRUE, FALSE, …The <= operator tests at most.
A2:A87 rows × 1 col: "INV-3301", "INV-3302", "INV-3303", "INV-3304", …A range: 7 cells in one column.
XLOOKUP(TRUE,ABS(B2:B8-E1)<=G1,A2:A8,"No match")"INV-3303"XLOOKUP: Searches one range and returns the matching item from another.- lookup_value
TRUE - lookup_array
ABS(B2:B8-E1)<=G1{7 rows × 1 col} FALSE, FALSE, TRUE, FALSE, … - return_array
A2:A87 rows × 1 col: "INV-3301", "INV-3302", "INV-3303", "INV-3304", … - if_not_found
"No match"
- lookup_value
In Google Sheets
The same formula works in Google Sheets: =XLOOKUP(TRUE,ABS(B2:B8-E1)<=G1,A2:A8,"No match"). We ran it in Google Sheets mode on the same data and it returns INV-3303.
Other ways to do it
Each one was run on the same example sheet.
| Formula | Result | When to use it |
|---|---|---|
| =INDEX(A2:A8,MATCH(TRUE,INDEX(ABS(B2:B8-E1)<=G1,0),0)) | INV-3303 | Every Excel version, without Ctrl+Shift+Enter. |
| =FILTER(A2:A8,ABS(B2:B8-E1)<=G1) | spills 2 × 1Different result | 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.
Try it yourself
Same data, empty answer cell. Your formula is graded instantly, then re-checked on changed data, so a typed-in number won't pass.
Find the first value within a tolerance of a target
E2, return the first invoice whose amount is within the tolerance in G1 of the payment in E1.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%.
Checked by running on 2026-10-30: every formula and result on this page was calculated by the Spreadsheet Practice formula engine, in both Excel and Google Sheets modes.