# Vlookup with a tolerance



## lsdientz (Jan 6, 2023)

I inherited two excel workbooks to manage.  They each have a column that concatenates an invoice number and a dollar amount for a discount.  There is a vlookup to match the concatenate columns but the issue is that the dollar amounts could be +/- .01 due to rounding differences.  Is there a way to do a vlookup that would take into consideration this tolerance or is there a different way that would be better?  Currently I have to manually review each row that does not match exactly but that is becoming very tedious.  Thank you!


----------



## ExceLoki (Jan 6, 2023)

you could try something like this
----------
Book1ABCDE1abc 12345.01testabc 12345testSheet1Cell FormulasRangeFormulaE1E1=INDEX(B1:B11,MATCH(LEFT(D1,9),LEFT(A1:A11,9),0),1)


----------



## lsdientz (Jan 6, 2023)

ExceLoki said:


> you could try something like this
> ----------


I will try that.  Thank you!


----------



## Alex Blakenburg (Jan 7, 2023)

Your profile shows 2 versions of Excel,  so here are 2 options.

Book1ABCDEFGHI1InvAmtLookupXlookupInvAmtLine no2A0001100.00  TEST250.0023A0002200.0155A0002400.0034A0003499.9966TEST4300.0045A0004400.00  A0002200.0056A0003500.0067Sheet1Cell FormulasRangeFormulaC2:C5C2=IFERROR(LOOKUP(2,
                                   1/ (($G$2:$G$6=A2) * ($H$2:$H$6<=(B2+0.01)) * ($H$2:$H$6>=(B2-0.01))),
                                   $I$2:$I$6),"")D2:D5D2=XLOOKUP(1,
                    ($G$2:$G$6=A2) * ($H$2:$H$6<=(B2+0.01)) * ($H$2:$H$6>=(B2-0.01)),
                    $I$2:$I$6,"")


----------



## Peter_SSs (Jan 7, 2023)

Welcome to the MrExcel board!

Another option for your 365 version.

23 01 07.xlsmABCDEFGHI1InvAmtInvAmtLine no2A0001100 TEST25023A0002200.015A000240034A0003499.996TEST430045A0004400 A000220056A00035006ToleranceCell FormulasRangeFormulaC2:C5C2=INDEX(FILTER(I$2:I$6,(G$2:G$6=A2)*(ABS(H$2:H$6-B2)<=0.01),""),1)


----------



## lsdientz (Monday at 10:21 AM)

Thank you all!  Crazy enough I do use both versions of excel.  I should have included in my post I was asking for the 365 version.  I appreciate your help!


----------



## Peter_SSs (Monday at 6:29 PM)

You're welcome. Glad we could help. Thanks for the follow-up.


----------

