Array lookup nearest match

hi there,

I need to do a lookup on two columns, "Total amount" with an exact match, and "Payment date" whichever is the closest match, and then return the values for "Payment date", "Cost centre" and "Supplier name"

Example xls attached, the lookup table is sheet "lookupdata" and example of the search result in sheet "search"

Purpose is to assist in manually reconciling transactions between two lists.

I'm guessing this is easiest solved as an array formula, but my memory and skills here are very rusty. Back in the day i'd have it knocked out in a jiffy but alas... please help!

Thanks!!
xeniumAsked:
Who is Participating?
 
Ejgil HedegaardConnect With a Mentor Commented:
Updated formula C3

=IF(ISERROR(MATCH($B3,lookupdata!$C$2:$C$20,0)),"null",INDEX(lookupdata!$B$2:$B$20,MATCH(MIN(ABS(lookupdata!$B$2:$B$20-$A3)+IF(lookupdata!$C$2:$C$20=$B3,0,10^5)),INDEX(ABS(lookupdata!$B$2:$B$20-$A3)+IF(lookupdata!$C$2:$C$20=$B3,0,10^5),,),0),1))

Open in new window

search-nearest-payment1.xlsx
0
 
Phillip BurtonDirector, Practice Manager and Computing ConsultantCommented:
Nothing attached.

However, if the columns are ordered in Total amount, then Payment date order, then you don't need to use an array - use a VLOOKUP(,,,true) instead with a helper column. It can return whether is the nearest below - but not the very nearest - for that, you would need an array and headache pills.

For example, if Total amount was in column B, and Payment date order was in column C, then column A could be

=format(b2,"0,000,000,000.00") & c2

And then the Vlookup would be

=vlookup(format(b2,"0,000,000,000.00") & c2,A$1:g$99999,2,true)

To retrieve column b - and then alter the 2 for each successive column,
0
 
Phillip BurtonDirector, Practice Manager and Computing ConsultantCommented:
Vlookup(,,,true) doesn't require exact matches - approximate ones will work.
0
Introducing Cloud Class® training courses

Tech changes fast. You can learn faster. That’s why we’re bringing professional training courses to Experts Exchange. With a subscription, you can access all the Cloud Class® courses to expand your education, prep for certifications, and get top-notch instructions.

 
xeniumAuthor Commented:
hi Phillip, thanks for the suggestion, I'll look at it, though I'd rather not manually sort the rows and reorder columns each time, so I think an array formula is what i need.

Sorry for missing the attachment, it's now been added here.

Cheers
search-nearest-payment.xlsx
0
 
Ejgil HedegaardCommented:
Try attached sheet
Array formula for C3.

=INDEX(lookupdata!$B$2:$B$20,MATCH(MIN(ABS(lookupdata!$B$2:$B$20-$A3)*IF(lookupdata!$C$2:$C$20=$B3,1,10^5)),INDEX(ABS(lookupdata!$B$2:$B$20-$A3)*IF(lookupdata!$C$2:$C$20=$B3,1,10^5),,),0),1)

Open in new window


For D3 and E3 the first range reference is changed.
search-nearest-payment.xlsx
0
 
gowflowCommented:
You mention

I need to do a lookup on two columns, "Total amount" with an exact match, and "Payment date" whichever is the closest match

1) What if no exact match for both what would you like to see ?

2)"Total amount" with an exact match
and
"Payment date" whichever is the closest match
What does this mean ?

gowflow
0
 
xeniumAuthor Commented:
hi Ejgil Hedegaard, that is great thanks. It is working apart from the exact match on amount, which is returning a match even if there is none. Is there a way to update this to return null if no match?

hi gowflow,
a) if no match on amount i want to return null.
b) if date is missing in the lookup table, i want to return any exact match on amount

See original attachment for example, copied below:
Date	Amount	lookup date	lookup cost code	lookup supplier
01-Nov-14	13.99	03-Nov-14	SMP	Robert Dyas

Open in new window


Cheers
0
 
xeniumAuthor Commented:
hi Ejgil Hedegaard, correction, I spoke too soon, the formula appeared to work but now I'm experiencing incorrect matches if i enter a date which exactly matches another record having an inexact match on amount. The result returned must be an exact match on amount, or null if there is no match on amount.

cheers
0
 
gowflowCommented:
ok fine still 1 thing.

When you say null you mean Blank I guess right ?
If amount matches but date does not what do we return ?
then if like your example 2 amounts matches with 2 dates but none of date matches we return the closest ? if date is outside the scope we return first occurrence or last occurrence of amount ?
gowflow
0
 
gowflowCommented:
Before you post your answer to my question here is my solution:

Put in Cell C2
=IF(ISNA(MATCH(search!B2,lookupdata!$C$1:$C$100,0)),"",IF(AND(A2="",MATCH(search!B2,lookupdata!$C$1:$C$100,0)),INDEX(lookupdata!$B$1:$B$100,MATCH(search!B2,lookupdata!$C$1:$C$100,0)),IFERROR(INDEX(lookupdata!$B$1:$B$100,MATCH(1,INDEX((A2<=lookupdata!$B$1:$B$100)*(lookupdata!$C$1:$C$100=B2),0),0)),INDEX(lookupdata!$B$1:$B$100,MATCH(search!B2,lookupdata!$C$1:$C$100,0)))))

In D2
=IF(ISNA(MATCH(search!B2,lookupdata!$C$1:$C$100,0)),"",IF(AND(A2="",MATCH(search!B2,lookupdata!$C$1:$C$100,0)),INDEX(lookupdata!$A$1:$A$100,MATCH(search!B2,lookupdata!$C$1:$C$100,0)),IFERROR(INDEX(lookupdata!$A$1:$A$100,MATCH(1,INDEX((A2<=lookupdata!$B$1:$B$100)*(lookupdata!$C$1:$C$100=B2),0),0)),INDEX(lookupdata!$A$1:$A$100,MATCH(search!B2,lookupdata!$C$1:$C$100,0)))))

In E2
=IF(ISNA(MATCH(search!B2,lookupdata!$C$1:$C$100,0)),"",IF(AND(A2="",MATCH(search!B2,lookupdata!$C$1:$C$100,0)),INDEX(lookupdata!$D$1:$D$100,MATCH(search!B2,lookupdata!$C$1:$C$100,0)),IFERROR(INDEX(lookupdata!$D$1:$D$100,MATCH(1,INDEX((A2<=lookupdata!$B$1:$B$100)*(lookupdata!$C$1:$C$100=B2),0),0)),INDEX(lookupdata!$D$1:$D$100,MATCH(search!B2,lookupdata!$C$1:$C$100,0)))))

all formulas enterd as array formula CTRL SHIFT ENTER
check the attached workbook. If no match on the amount the whole row is blank !

gowflow
search-nearest-payment-V01.xlsx
0
 
xeniumAuthor Commented:
hi Ejgil Hedegaard, great thanks again. This looks fine. See attached the same with specific test cases which all seem to pass. If nothing else crops up I'll close this question shortly. Meanwhile, if you can also give a brief verbal description of the method (the formula) that would be helpful too.

hi gowflow, yes i mean blank  (sorry confused with VB, anyway i mean the value after the contents of a cell is deleted, or never been populated). Re cases see attached test case grid.

Cheers
search-nearest-payment-ee-v2-with-test-c
0
 
gowflowCommented:
sorry cannot open the file you posted.
gowflow
0
 
xeniumAuthor Commented:
hi gloflow, thanks for your answer too. I ran your formula through the same test cases and it seems to works for all except case 5.3 which seems to fail. If this is easy to fix please let me know, as there maybe something useful to learn in an alternative method.

cheers
0
 
xeniumAuthor Commented:
attempting to re-attach...

sorry i'm having trouble not sure why it's not uploading right ...hang on...
search-nearest-payment-ee-v2-with-test-c
search-nearest-payment-ee-v2-with-test-c
search-nearest-payment-ee-v2-with-test-c
0
 
gowflowCommented:
Can you give me feedback on my file please.
gowflow
0
 
gowflowCommented:
ok our answers crossed. let me look at 5.3
gowlfow
0
 
xeniumAuthor Commented:
Sorry having trouble getting a downloadable version to upload. See instead this copy on google docs which seems to work (but i have not checked all the test cases on this copy)
https://docs.google.com/spreadsheets/d/1Eov8eVCNerabqag_tBs3LkAc7orHjzF6RAjTzAeA5pg
0
 
xeniumAuthor Commented:
gowflow, i took your formulae you posted and inserted them into the xls i had already. In this case it should be the same as your attached file. But see link above for the test cases if you can't access the latest xls.
0
 
gowflowCommented:
Well 5.3 is because you never answered my question on ID: 40528149

I specifically asked if

then if like your example 2 amounts matches with 2 dates but none of date matches we return the closest ?

So I took that when you have the same amount and date is in the range in case of 5.3
date of 30-Mar-2014 is between
first occurrence 25-Mar-2014
and next occurrence 03-Nov-2014
so logic says to pick the next interval which is why it shows 03-Nov-2014

So your answer in this case would be to always show first occurrence ???
waiting for your answer
gowflow
0
 
gowflowCommented:
Yes I was able to access the file.
see my last comment
gowflow
0
 
xeniumAuthor Commented:
In the question i say "Payment date" whichever is the closest match

I did not specify any direction forward or back in time, so I'm assuming either way applies.

So I'm reading case 5.3 as 30 Mar 2014 is closer to 25 Mar 2014 (5 days) than it is to 3 Nov 2014 (a few months)

I hope this helps. Thanks for bringing up this clarification.

cheers
0
 
xeniumAuthor Commented:
Regarding a philosophical discussion on the direction of time, that will have to move over to the lounge... ;-)
0
 
Ejgil HedegaardConnect With a Mentor Commented:
The upload problem is because EE does not accept file names longer than 40 characters.

Her is a description of the formula
=IF(ISERROR(MATCH($B3,lookupdata!$C$2:$C$20,0)),"null",INDEX(lookupdata!$B$2:$B$20,MATCH(MIN(ABS(lookupdata!$B$2:$B$20-$A3)+IF(lookupdata!$C$2:$C$20=$B3,0,10^5)),INDEX(ABS(lookupdata!$B$2:$B$20-$A3)+IF(lookupdata!$C$2:$C$20=$B3,0,10^5),,),0),1))

IF(ISERROR(MATCH($B3,lookupdata!$C$2:$C$20,0)),"null" checks if an exact match exist for amount, if not return "null", change "null" to "" if you want the cell to look empty.

INDEX(lookupdata!$B$2:$B$20, is the range to find the return value.
The match function following returns the row number, and the 1 at the end of the formula the column number for the index function.

In the match function for the row number MIN(ABS(lookupdata!$B$2:$B$20-$A3)+IF(lookupdata!$C$2:$C$20=$B3,0,10^5)) is the value to look for.
ABS(lookupdata!$B$2:$B$20-$A3) finds the minimum date distance, ABS turns it to a positive number.
+IF(lookupdata!$C$2:$C$20=$B3,0,10^5) adds 0 if the amount value fit, otherwise something large, here 10^5.
MIN sets that it is the minimum value to search for.

The match function needs a range to search, and the range is the same as used for the value to look for ABS(lookupdata!$B$2:$B$20-$A3)+IF(lookupdata!$C$2:$C$20=$B3,0,10^5)
That is put inside an index function to create a range.
When the index function has no row and/or column specification like INDEX(Range,,), it returns the entire range and not a specific value within the range.
0
 
gowflowCommented:
ok here it is
gowflow
search-nearest-payment-V02.xlsm
0
 
xeniumAuthor Commented:
gowflow, case 5.3 seems to show no change:

Expected result: 25-Mar-14, Personal, ebay
Actual result: 03-Nov-14, SMP, Robert Dyas

I can't justify much more testing when the answer from Ejgil Hedegaard seems to be working. If there is an aspect of your answer that represents a possible enhancement please highlight this so I can learn from it.

Thanks again.
0
 
gowflowCommented:
Fine no problem. Pls go ahead and close it as you feel appropriate.
gowflow
0
 
xeniumAuthor Commented:
Great thanks for all your effort and inputs.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.