Lookup & Reference
Find candidate amounts within an inclusive tolerance band
Compare numeric amounts with a target and flag ambiguous matches before selecting a candidate.
Use a tolerance to find candidates, not prove a match
For each of the four amounts B2:B5, C2:C5 calculates ABS(amount-target), using target F2. A gap equal to tolerance F3 is included. All amounts, target and tolerance must be numeric, tolerance must be nonnegative, and invoice labels must be present. Keep the helper formulas intact.
Amount proximity alone does not establish that a payment belongs to an invoice. Check payer, currency, date and other identifiers separately. A symmetric tolerance permits both underpayments and overpayments; use a directional rule when only fee deductions are allowed.
The candidate formula below returns Ambiguous when more than one amount is inside the band, even if one is closer. It therefore avoids silently choosing the first tied invoice.
| Open Invoice (A) | Invoice Amount (B) | Gap from $4,838 Wire (C) | Within +/- $15? |
|---|---|---|---|
| INV-801 | $3,200 | $1,638 | No |
| INV-802 | $4,850 | $12 (<= $15!) | Matched -> "INV-802"! |
| INV-803 | $4,910 | $72 | No (> $15) |
| INV-804 | $6,100 | $1,262 | No |
Return a unique candidate within the inclusive tolerance
No candidates returns Unmatched (> Tol); multiple candidates return Ambiguous. Zero tolerance requires an exact amount match. Negative amounts are allowed for this arithmetic example, so verify the business meaning of credits separately.
=IF(OR(COUNT(B2:B5)<>4,COUNT(F2:F3)<>2,COUNTBLANK(A2:A5)>0),"Invalid inputs",IF(F3<0,"Invalid inputs",IF(COUNTIF(C2:C5,"<="&F3)=0,"Unmatched (> Tol)",IF(COUNTIF(C2:C5,"<="&F3)>1,"Ambiguous",INDEX(A2:A5,XMATCH(MIN(C2:C5),C2:C5,0))))))Count all candidate invoices falling inside +/- F3 (ambiguity check)
If COUNTIF(C2:C5, "<=" & F3) > 1, two open invoices sit inside the fee window and need manual review!
=IF(OR(COUNT(B2:B5)<>4,COUNT(F2:F3)<>2,COUNTBLANK(A2:A5)>0),"Invalid inputs",IF(F3<0,"Invalid inputs",COUNTIF(C2:C5,"<="&F3)))Try the example
Use the original values or change them to test your understanding.