Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
Solved

# Array lookup nearest match

Posted on 2014-12-31
Medium Priority
117 Views
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!!
0
Question by:xenium
[X]
###### Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

• Help others & share knowledge
• Earn cash & points
• 12
• 10
• 3
• +1

LVL 24

Expert Comment

ID: 40525574
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

LVL 24

Expert Comment

ID: 40525576
Vlookup(,,,true) doesn't require exact matches - approximate ones will work.
0

Author Comment

ID: 40525759
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

LVL 23

Expert Comment

ID: 40526181
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)
``````

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

LVL 31

Expert Comment

ID: 40526734
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

Author Comment

ID: 40527914
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
``````

Cheers
0

Author Comment

ID: 40527963
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

LVL 23

Accepted Solution

Ejgil Hedegaard earned 2000 total points
ID: 40528030
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))
``````
search-nearest-payment1.xlsx
0

LVL 31

Expert Comment

ID: 40528149
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

LVL 31

Expert Comment

ID: 40528181
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

Author Comment

ID: 40528189
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

LVL 31

Expert Comment

ID: 40528205
sorry cannot open the file you posted.
gowflow
0

Author Comment

ID: 40528207
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

Author Comment

ID: 40528212
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

LVL 31

Expert Comment

ID: 40528219
Can you give me feedback on my file please.
gowflow
0

LVL 31

Expert Comment

ID: 40528221
ok our answers crossed. let me look at 5.3
gowlfow
0

Author Comment

ID: 40528226
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)
0

Author Comment

ID: 40528230
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

LVL 31

Expert Comment

ID: 40528232
Well 5.3 is because you never answered my question on ID: 40528149

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 ???
gowflow
0

LVL 31

Expert Comment

ID: 40528235
Yes I was able to access the file.
see my last comment
gowflow
0

Author Comment

ID: 40528248
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

Author Comment

ID: 40528260
Regarding a philosophical discussion on the direction of time, that will have to move over to the lounge... ;-)
0

LVL 23

Assisted Solution

Ejgil Hedegaard earned 2000 total points
ID: 40528292
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

LVL 31

Expert Comment

ID: 40528294
ok here it is
gowflow
search-nearest-payment-V02.xlsm
0

Author Comment

ID: 40528345
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

LVL 31

Expert Comment

ID: 40528507
Fine no problem. Pls go ahead and close it as you feel appropriate.
gowflow
0

Author Closing Comment

ID: 40528621
Great thanks for all your effort and inputs.
0

## Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

How to get Spreadsheet Compare 2016 working with the 64 bit version of Office 2016
This article describes a serious pitfall that can happen when deleting shapes using VBA.
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to usâ€¦
###### Suggested Courses
Course of the Month9 days, 16 hours left to enroll