+ Reply to Thread
Results 1 to 7 of 7

Closest match on Multiple Criteria

  1. #1
    Registered User
    Join Date
    04-09-2014
    Location
    United Kingdom
    MS-Off Ver
    Excel 2010
    Posts
    3

    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. #2
    Registered User
    Join Date
    04-09-2014
    Location
    United Kingdom
    MS-Off Ver
    Excel 2010
    Posts
    3

    Re: Closest match on Multiple Criteria

    Is anyone able to assist with this query please?

  3. #3
    Forum Expert
    Join Date
    09-01-2012
    Location
    Norway
    MS-Off Ver
    Office 365
    Posts
    2,841

    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?


    Hmm... uploading not working right now.

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


    In H2:
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    <----- If you were helped by my posts you can say "Thank you" by clicking the star symbol down to the left

    If the problem is solved, finish of the thread by clicking SOLVED under Thread Tools
    I don't wish to leave you with no answer, yet I sometimes miss posts. If you feel I forgot you, remind me with a PM or just bump the thread.

  4. #4
    Forum Moderator - RIP Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    29,464

    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: copy to clipboard
    Please Login or Register  to view this content.

    H2:
    Formula: copy to clipboard
    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)
    Richard Buttrey

    RIP - d. 06/10/2022

    If any of the responses have helped then please consider rating them by clicking the small star icon below the post.

  5. #5
    Registered User
    Join Date
    04-09-2014
    Location
    United Kingdom
    MS-Off Ver
    Excel 2010
    Posts
    3

    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. #6
    Registered User
    Join Date
    04-09-2014
    Location
    United Kingdom
    MS-Off Ver
    Excel 2010
    Posts
    3

    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. #7
    Forum Expert
    Join Date
    09-01-2012
    Location
    Norway
    MS-Off Ver
    Office 365
    Posts
    2,841

    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.
    Attached Files Attached Files

  8. #8
    Forum Expert
    Join Date
    09-01-2012
    Location
    Norway
    MS-Off Ver
    Office 365
    Posts
    2,841

    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: copy to clipboard
    Please Login or Register  to view this content.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] Multiple criteria lookup - First match :exact" and second match "Closest"
    By mweichorn in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 07-16-2013, 12:14 PM
  2. Replies: 6
    Last Post: 01-28-2012, 06:59 PM
  3. Replies: 2
    Last Post: 01-28-2012, 05:26 PM
  4. closest match from multiple criteria
    By wongja in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 07-31-2010, 01:37 AM
  5. Replies: 12
    Last Post: 05-20-2010, 06:11 AM

Bookmarks

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