Array lookup nearest match

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,

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

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

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)

Select allOpen in new window

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

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

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

Select allOpen in new window

Cheers

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

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

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

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

sorry cannot open the file you posted.
gowflow

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

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

Can you give me feedback on my file please.
gowflow

ok our answers crossed. let me look at 5.3
gowlfow

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

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.

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

Yes I was able to access the file.
see my last comment
gowflow

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

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

ok here it is
gowflow
search-nearest-payment-V02.xlsm

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.

Fine no problem. Pls go ahead and close it as you feel appropriate.
gowflow

Great thanks for all your effort and inputs.