Find neareast value with multiple criteria

**mrsogmax76** · 01-18-2012, 09:52 PM

Good Day,

I am working with a workbook trying to marry payment amounts with invoices from two separate spreadsheets. Criteria for the current look up is the customer, invoice amount, and document type (invoice vs credit). The issue I am having is marrying invoices with payments that me be off a couple of cents (+/-) due to rounding. Below is what I'm currently working with, but I am at a lost beyond this. Thank you.

INDEX(altref,MATCH(1,(rtype="Invoice")*(amount=$K143)*(customer=$C143),0))

altref = invoice value I want retruned
rtype = document is invoice vs credit

Example

Spreadsheet 1 Customer 1 invoice 21-12345-01 amount 12.33

Spreadsheet 2 Customer 1 payment 12.34 Invoice N/A with above formula
Should still return value 21-12345-01

**bebo021999** · 01-19-2012, 01:54 AM

It is so hard to find solution unless you post a suporrting file!
Anyway, try to replace MATCH(1,...) with MATCH(2,...)
If it does not work, post a dummy file,
Regards,

**mrsogmax76** · 01-19-2012, 03:03 PM

Please see attached dummy file. Any assistance you can provide is deeply appreciated.

**bebo021999** · 01-19-2012, 10:34 PM

Originally Posted by mrsogmax76

Please see attached dummy file. Any assistance you can provide is deeply appreciated.

How would you like to round?
Assuming round to 1 decimal. Ex: 15,583.31 -> 15,583.39 = 15,583.40
Amount should be rounded up: = ROUNDUP(amount,1)
Anyway, 1st line of amount range contains text (column title), so re-define range of the ALL name to start from row(2) (altref, customer, invoice,...) OR use ROUNDUP(IF(ISNUMBER(amount),amount,0),1) without re-define name.
The formula to get invoice ref should be as follow:

=IF(ISERROR(INDEX(altref,MATCH(1,(rtype="Invoice")*(ROUNDUP(IF(ISNUMBER(amount),amount,0),1)=ROUNDUP($D5,1))*(customer=$C5),0))),"Unidentified",INDEX(altref,MATCH(1,(rtype="Invoice")*(ROUNDUP(IF(ISNUMBER(amount),amount,0),1)=ROUNDUP($D5,1))*(customer=$C5),0)))

Hope this works.

**mrsogmax76** · 01-20-2012, 12:20 PM

That worked like a charm. Even better, it's simple and I actually understand what it's doing.

Only issue is that it's nested in another formula so now there are to many conditions, and I get an error. Is it possible to condense this, so that it will work if works for nearest value (+/-) and exact matches. Thank you.

**dilipandey** · 01-19-2012, 01:59 AM

Hi mrsogmax76,

12.33 changed to 12.34 on sheet 2.. creates confusion.

As suggested by bebo, upload a sample file. Thanks.

Regards,
DILIPandey

<click on below 'star' if this helps>

mrsogmax76 is offline
Registered User