# Closest match on Multiple Criteria

1. ## Closest match on Multiple Criteria

Closest Date ad Price - 2.xlsx

I'm usually pretty good with Excel but this one has me stumped.
What I am trying to do is based on a SKU and its Return date match bring back the closest sale date and the sale price of the SKU on that closest date.
Figuring an Index Match formula would be best however the issue I'm coming up against is that the date is trying to find an exact match.
I have also tried maliplulating vlookup formulas to no avail.

Column A shows the SKU
Column B shows the Date Sold
Column C shows the Sales Price

Column E Shows the SKU needing to be looked up
Column F Shows the return date

Column G I would like to return the closest date to the return date that the SKU in column E shows
Column H I would like to return the price of the SKU on the closest date on column G

I know this can be done, I'm just having trouble executing this one. Any help would be greatly appreciated.

Sample Attached

2. ## Re: Closest match on Multiple Criteria

Is anyone able to assist with this query please?

3. ## Re: Closest match on Multiple Criteria

I think I got it right but it was a bit of a struggle with some #N/A and stuff.
These are array formulas (enter with Ctrl + Shift + Enter) of course and 34000 rows takes a couple of seconds to recalc.

I assume you are gonna ask me to add a condition that the price can't be zero next?

In G2:
Formula:
`Please Login or Register  to view this content.`

In H2:
Formula:
`Please Login or Register  to view this content.`

4. ## Re: Closest match on Multiple Criteria

Hi,

If you sort A:C with A as the first key and B as the second, both ascending, AND if you are happy to accept the nearest date prior to the column F date then
G2:
Formula:
`Please Login or Register  to view this content.`

H2:
Formula:
`Please Login or Register  to view this content.`

Could be simplified by using helper columns instead of incorporating elements like MATCH(\$E2,\$A\$1:\$A\$34429,FALSE)

5. ## Re: Closest match on Multiple Criteria

Thanks Jacc I've tried this out and it works a charm.
Could you explain in words exactly what is happening and how with these formulas, for next time.

6. ## Re: Closest match on Multiple Criteria

Thanks Jacc I've tried this out and it works a charm.
Could you explain in words exactly what is happening and how with these formulas, for next time.

7. ## Re: Closest match on Multiple Criteria

Glad to hear it worked!
I'll try to explain it to you here. Usually the Evaluate Formula feature under the Formulas banner in Excel is useful for see how array formulas works but because of your large data set I can't really recommend it. One option would be to create a sample sheet where you scale down the data to maybe 20 rows or so and then use the Evaluate Formula feature.
As you may already know, an array formula makes functions that normally only accept single cell inputs, accept large ranges of cells aas inputs and performs calculations on these.

The formula:
IF(COUNTIF(\$A\$2:\$A\$34429,E2)=0,"-",INDEX(B:B,MIN(IF(IF(IFERROR(\$A\$2:\$A\$34429,0)=E2,ABS(\$B\$2:\$B\$34429-F2))=MIN(IF(IFERROR(\$A\$2:\$A\$34429,0)=E2,ABS(\$B\$2:\$B\$34429-F2))),ROW(\$B\$2:\$B\$34429)))))

The IF(COUNTIF(\$A\$2:\$A\$34429,E2)=0,"-", part is just for checking that that particular SKU exist in the list at all. If not there is no reason to proceed and the result is just "-". If that SKU do exist in the list the rest of the formula executes. I can't use IFERROR because if the value does not exist the rest of the formula will execute and return the first date in column B. Also this speeds things up considerably.

aa The IFERROR creates a new array from A2:A34429 where all the #N/A have been replaced with zeroes. Some functions can't handle #N/A so without this, the entire results turns to #N/A.

bb The ABS(\$B\$2:\$B\$34429-F2) take the absolute value of the difference between the date in F2 and the date in column A. The difference can be positive or negative but the ABS turns them all into positive values so that the MIN can be applied later. If ABS was not used the MIN function would pick out the largest negative date difference instead of the smallest date difference.

cc The purpose of the IF(aa=E2,bb) is to only get the date differences from the rows where the SKU is correct. The result is an array with only the date differences for the SKU of interest, the rest of the array is filled with FALSE.

dd The MIN(cc) of course gets the minimum date difference of the resulting array. MIN ignores FALSE.

ee The IF(IFERROR(\$A\$2:\$A\$34429,0)=E2,ABS(\$B\$2:\$B\$34429-F2)) looks and works the same as what has been described above. The result is an array with the date differences for all the SKU of interest.

ff The IF(ee=dd,ROW(\$B\$2:\$B\$34429)) compares all the date differences for which the SKU is correct with the smallest date difference for which the SKU is correct. If they are the same, it will put the row no of where the match was found in the resulting array. The rest of the array will be made up of FALSE. Basically, it locates the row of the smallest, valid date difference.

gg The MIN(ff) is just for picking out a single value (a row no in this case) from the previous result array. Most likely there is just one value anyway and using MAX would work equally well. If there were multiple values (two date differences were the same) either one of them would provide the correct answer.

The INDEX(B:B,gg) takes the row no gg and returns the value from column B for that row no.

8. ## Re: Closest match on Multiple Criteria

Reading through my explanation, I realized that the last step is just blubber. Instead of picking out the row no (at ff) I might just as well pick the final value.
So here is the slightly slimmed down version:
Formula:
`Please Login or Register  to view this content.`

##### Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1