+ Reply to Thread
Results 1 to 21 of 21

Need help in multi criteria find and match value

  1. #1
    Registered User
    Join Date
    09-05-2016
    Location
    Vancouver, Canada
    MS-Off Ver
    2013
    Posts
    10

    Need help in multi criteria find and match value

    Hi,

    I have a question about finding a corresponding rate from Sheet2. Can someone provide a solution? Thank you very much.

    Sheet2 is a rate table. "Effective Dates" means "the rate is effective after that date".
    Sheet1 is a working table. I need to find the rate from Sheet2 according to "Employer, Effective Date", and "HoursType".

    Sheet1 is worksheet, Sheet is rate table.

    Sheet1.JPGSheet2.JPG
    Attached Files Attached Files
    Last edited by alvin8866; 09-21-2016 at 01:16 AM.

  2. #2
    Forum Expert JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: Need help in multi criteria find and match value

    Attach a sample workbook. Make sure your desired results are shown, mock them up manually if necessary so we know when a formula comes up with the same answer you did manually.

    Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.
    _________________
    Microsoft MVP 2010 - Excel
    Visit: Jerry Beaucaire's Excel Files & Macros

    If you've been given good help, use the icon below to give reputation feedback, it is appreciated.
    Always put your code between code tags. [CODE] your code here [/CODE]

    ?None of us is as good as all of us? - Ray Kroc
    ?Actually, I *am* a rocket scientist.? - JB (little ones count!)

  3. #3
    Valued Forum Contributor
    Join Date
    06-21-2013
    Location
    Bangladesh
    MS-Off Ver
    Excel 2000, Excel XP, Excel 2002, Excel 2007, Excel 2010, Excel 2013
    Posts
    975

    Re: Need help in multi criteria find and match value

    You can use this array formula :
    Please Login or Register  to view this content.
    Confirm Ctrl+Shift+Enter. Here replace Sheet1 with the name of your Sheet1 and Sheet2 with the name of your Sheet2.

  4. #4
    Registered User
    Join Date
    09-05-2016
    Location
    Vancouver, Canada
    MS-Off Ver
    2013
    Posts
    10

    Re: Need help in multi criteria find and match value

    Quote Originally Posted by sanram View Post
    You can use this array formula :
    Please Login or Register  to view this content.
    Confirm Ctrl+Shift+Enter. Here replace Sheet1 with the name of your Sheet1 and Sheet2 with the name of your Sheet2.
    Thanks Sanram, but the code does not work.

  5. #5
    Valued Forum Contributor
    Join Date
    06-21-2013
    Location
    Bangladesh
    MS-Off Ver
    Excel 2000, Excel XP, Excel 2002, Excel 2007, Excel 2010, Excel 2013
    Posts
    975

    Re: Need help in multi criteria find and match value

    Your code is not working because you didn't change the sheet name as I suggested above. Also I have seen that you have changed the column references in 1st sheet. If you do so then nobody will be able to help you. Here is the code with your sheet names :
    Please Login or Register  to view this content.

  6. #6
    Registered User
    Join Date
    09-05-2016
    Location
    Vancouver, Canada
    MS-Off Ver
    2013
    Posts
    10
    Quote Originally Posted by sanram View Post
    Your code is not working because you didn't change the sheet name as I suggested above. Also I have seen that you have changed the column references in 1st sheet. If you do so then nobody will be able to help you. Here is the code with your sheet names :
    Please Login or Register  to view this content.
    Hi Sanram, thank you for your comments. I changed the coding properly to fit my worksheet. The reason it is not working is that the "rate" sheet has different effective date, and none of them match the date on working sheet. I want to retrieve the date after certain effective date. Your formula works perfectly when there is no effective date involved.

  7. #7
    Registered User
    Join Date
    09-05-2016
    Location
    Vancouver, Canada
    MS-Off Ver
    2013
    Posts
    10

    Re: Need help in multi criteria find and match value

    I think this is needed to be done by VBA FUNCTION to customise my own formula.

  8. #8
    Valued Forum Contributor
    Join Date
    06-21-2013
    Location
    Bangladesh
    MS-Off Ver
    Excel 2000, Excel XP, Excel 2002, Excel 2007, Excel 2010, Excel 2013
    Posts
    975

    Re: Need help in multi criteria find and match value

    Ok. I didn't get that before that you need to match next effective date. Then use this array formula :
    Please Login or Register  to view this content.
    Confirm Ctrl+Shift+Enter.

    See the attachment.
    Attached Files Attached Files

  9. #9
    Forum Expert Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: Need help in multi criteria find and match value

    Quote Originally Posted by sanram View Post
    =INDEX(Rate!$E:$E,MATCH(1,($B2=Rate!$B:$B)*($C2=Rate!$C:$C)*($D2<=Rate!$D:$D),0))
    You should avoid using entire columns as range references in array formulas.

    Every cell referenced gets evaluated:

    ($B2=Rate!$B:$B), every cell in column B is being evaluated, that's 1,048,576 cells.
    ($C2=Rate!$C:$C), every cell in column C is being evaluated, that's 1,048,576 cells.
    ($D2<=Rate!$D:$D), every cell in column D is being evaluated, that's 1,048,576 cells.

    That's over 3 million cells just for that one formula!

    Use smaller specific ranges.
    Biff
    Microsoft MVP Excel
    Keep It Simple Stupid

    Let's Go Pens. We Want The Cup.

  10. #10
    Valued Forum Contributor
    Join Date
    06-21-2013
    Location
    Bangladesh
    MS-Off Ver
    Excel 2000, Excel XP, Excel 2002, Excel 2007, Excel 2010, Excel 2013
    Posts
    975

    Re: Need help in multi criteria find and match value

    @ Tony Valko :
    As far as I know Index match stop executing when they found the result. Do I know this wrong?

  11. #11
    Forum Expert Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: Need help in multi criteria find and match value

    Array formulas will evaluate EVERY cell they reference.

    So, if you refer to an entire column then EVERY cell in that column will be evaluated.

  12. #12
    Valued Forum Contributor
    Join Date
    06-21-2013
    Location
    Bangladesh
    MS-Off Ver
    Excel 2000, Excel XP, Excel 2002, Excel 2007, Excel 2010, Excel 2013
    Posts
    975

    Re: Need help in multi criteria find and match value

    Ok. Got it. Thanks for the info. I knew that SUM, SUMPRODUCT, PRODUCT, etc calculate whole reference. But I didn't know that INDEX/MATCH do the same in array formulas.

  13. #13
    Forum Expert Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: Need help in multi criteria find and match value

    Quote Originally Posted by sanram View Post
    Ok. Got it. Thanks for the info. I knew that SUM, SUMPRODUCT, PRODUCT, etc calculate whole reference. But I didn't know that INDEX/MATCH do the same in array formulas.
    Some functions are "smart" and will only evaluate the used range.

    SUM is one of those.

    SUMPRODUCT will evaluate EVERY cell referenced.

    MATCH will do both!

    When used like this:

    MATCH(A1,B:B,0)

    It will stop if/when it finds a match.

    However, when used like this:

    MATCH(1,(B:B="X")*(C:C="Y")*(D:D="Z"),0)

    It will evaluate EVERY cell referenced.

  14. #14
    Valued Forum Contributor
    Join Date
    06-21-2013
    Location
    Bangladesh
    MS-Off Ver
    Excel 2000, Excel XP, Excel 2002, Excel 2007, Excel 2010, Excel 2013
    Posts
    975

    Re: Need help in multi criteria find and match value

    That's really a very helpful info for me. Thanks.

  15. #15
    Forum Expert Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: Need help in multi criteria find and match value








    ---------------

  16. #16
    Registered User
    Join Date
    09-05-2016
    Location
    Vancouver, Canada
    MS-Off Ver
    2013
    Posts
    10

    Re: Need help in multi criteria find and match value

    I cannot find any built-in functions that serve my purpose. I want to find the latest rate relating to my EffectiveDate. As a result, I wrote a VBA Function to work for my goal.

    Please Login or Register  to view this content.

  17. #17
    Valued Forum Contributor
    Join Date
    06-21-2013
    Location
    Bangladesh
    MS-Off Ver
    Excel 2000, Excel XP, Excel 2002, Excel 2007, Excel 2010, Excel 2013
    Posts
    975

    Re: Need help in multi criteria find and match value

    Quote Originally Posted by sanram View Post
    Ok. I didn't get that before that you need to match next effective date. Then use this array formula :
    Please Login or Register  to view this content.
    Confirm Ctrl+Shift+Enter.

    See the attachment.
    Didn't that work?

  18. #18
    Valued Forum Contributor
    Join Date
    06-21-2013
    Location
    Bangladesh
    MS-Off Ver
    Excel 2000, Excel XP, Excel 2002, Excel 2007, Excel 2010, Excel 2013
    Posts
    975

    Re: Need help in multi criteria find and match value

    Ok. I have seen your code and now I can understand that you are trying to match previous effective date. But from the beginning you are saying that next effective. That's why you didn't get any correct answer.

  19. #19
    Valued Forum Contributor
    Join Date
    06-21-2013
    Location
    Bangladesh
    MS-Off Ver
    Excel 2000, Excel XP, Excel 2002, Excel 2007, Excel 2010, Excel 2013
    Posts
    975

    Re: Need help in multi criteria find and match value

    This built in function will do what you need :
    Please Login or Register  to view this content.
    Confirm Ctrl+Shift+Enter as it is an array formula.

  20. #20
    Registered User
    Join Date
    09-05-2016
    Location
    Vancouver, Canada
    MS-Off Ver
    2013
    Posts
    10

    Re: Need help in multi criteria find and match value

    Quote Originally Posted by sanram View Post
    Ok. I have seen your code and now I can understand that you are trying to match previous effective date. But from the beginning you are saying that next effective. That's why you didn't get any correct answer.
    Yes Sanram, sorry for any confusion. In this project, I need to keep the historical data, and also use them in calculation. I think if I sort the effective date on the "RateSheet" from newest to oldest, your code will work fine. The code will just find the first matching rate and stop.

  21. #21
    Valued Forum Contributor
    Join Date
    06-21-2013
    Location
    Bangladesh
    MS-Off Ver
    Excel 2000, Excel XP, Excel 2002, Excel 2007, Excel 2010, Excel 2013
    Posts
    975

    Re: Need help in multi criteria find and match value

    My last formula is giving the same answer as like as your function. No sorting is needed for this. I have matched the result with your last attachment.

+ 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] Need a multi-conditional index match statement with > and < criteria
    By tankgrrlz in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 06-07-2016, 11:30 AM
  2. New & Lost: Multi Criteria & Multi Row INDEX, SMALL, MATCH...
    By morleyp in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 05-23-2014, 12:40 PM
  3. Replies: 6
    Last Post: 04-30-2014, 02:42 AM
  4. Help : Multi criteria with MATCH or LOOKUP formula
    By clonedarkman in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 03-20-2014, 10:47 AM
  5. Index with imbedded multi-criteria Match
    By DFELS in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 08-29-2013, 06:46 AM
  6. Multi Criteria Index and Match - Getting #NA Error
    By abbati77 in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 10-07-2012, 03:22 AM
  7. Find Value If Multi Criteria Matches Index
    By exc4libur in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 05-05-2011, 09:05 PM

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