Solved

Array lookup nearest match

Posted on 2014-12-31
27
86 Views
Last Modified: 2015-01-02
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
Comment
Question by:xenium
  • 12
  • 10
  • 3
  • +1
27 Comments
 
LVL 24

Expert Comment

by:Phillip Burton
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

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

Author Comment

by:xenium
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 21

Expert Comment

by:Ejgil Hedegaard
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)

Open in new window


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

Expert Comment

by:gowflow
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

by:xenium
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

Open in new window


Cheers
0
 

Author Comment

by:xenium
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 21

Accepted Solution

by:
Ejgil Hedegaard earned 500 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))

Open in new window

search-nearest-payment1.xlsx
0
 
LVL 29

Expert Comment

by:gowflow
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 29

Expert Comment

by:gowflow
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

by:xenium
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 29

Expert Comment

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

Author Comment

by:xenium
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
Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

 

Author Comment

by:xenium
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 29

Expert Comment

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

Expert Comment

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

Author Comment

by:xenium
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)
https://docs.google.com/spreadsheets/d/1Eov8eVCNerabqag_tBs3LkAc7orHjzF6RAjTzAeA5pg
0
 

Author Comment

by:xenium
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 29

Expert Comment

by:gowflow
ID: 40528232
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
 
LVL 29

Expert Comment

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

Author Comment

by:xenium
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

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

Assisted Solution

by:Ejgil Hedegaard
Ejgil Hedegaard earned 500 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 29

Expert Comment

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

Author Comment

by:xenium
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 29

Expert Comment

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

Author Closing Comment

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

Featured Post

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

What is a Form List Box? (skip if you know this) The forms List Box is the alternative to the ActiveX list box. If you are using excel 2007, you first make sure you have a developer tab (click the Orb)->"Excel Options"->Popular->"Show Developer tab…
This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.

705 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now