Results 1 to 5 of 5

Thread: Lookup on Multiple Criteria

  1. #1
    Junior Member
    Join Date
    Sep 2011
    Posts
    13
    Rep Power
    0

    Lookup on Multiple Criteria

    Dear Experts,

    I have a work book in sheet-1 i have made the grids based on certain multiple parameters. In sheet-2 the data sheet the values to be mapped from column F2 TO K2 based on the parameters set on the sheet-1. Where ever there is no values available it should be displayed as N/A. Can some one help me out to get a code to do this task automatically using a macro.

    Attached the sheet for your kind reference.


    Thanks in Advance

    R.Ramakrishnan
    Attached Files Attached Files

  2. #2
    Administrator Excel Fox's Avatar
    Join Date
    Mar 2011
    Posts
    1,401
    Rep Power
    10
    Try this formula in Grid!F2 and copy the formula across the adjacent cells

    =SUMPRODUCT((Grid!$A$2:$A$1000=Data!$B2)*(Grid!$D$ 2:$D$1000<=Data!$D2)*(Grid!$E$2:$E$1000>=Data!$D2) *(Grid!$F$2:$F$1000<=Data!$E2)*(Grid!$G$2:$G$1000> =Data!$E2)*(Grid!H$2:H$1000))
    A dream is not something you see when you are asleep, but something you strive for when you are awake.

    It's usually a bad idea to say that something can't be done.

    The difference between dream and aim, is that one requires soundless sleep to see and the other requires sleepless efforts to achieve

    Join us at Facebook

  3. #3
    Administrator Admin's Avatar
    Join Date
    Mar 2011
    Posts
    1,122
    Rep Power
    10
    Hi RK,

    Anoher one..

    In F2 and copied down and across,

    =LOOKUP(2,1/((Grid!$A$2:$A$25=$B2)*($C2>=Grid!$B$2:$B$25)*($C2 <=Grid!$C$2:$C$25)*($D2>=Grid!$D$2:$D$25)*($D2<=Gr id!$E$2:$E$25)*($E2>=Grid!$F$2:$F$25)*($E2<=Grid!$ G$2:$G$25)),Grid!H$2:H$25)


    Using INDEX/MATCH

    =INDEX(Grid!H$2:H$25,MATCH(1,IF((Grid!$A$2:$A$25=$ B2)*($C2>=Grid!$B$2:$B$25)*($C2<=Grid!$C$2:$C$25)* ($D2>=Grid!$D$2:$D$25)*($D2<=Grid!$E$2:$E$25)*($E2 >=Grid!$F$2:$F$25)*($E2<=Grid!$G$2:$G$25),1),0))

    It's an array formula. Confirm with CTRL + SHIFT + ENTER, not just ENTER.
    Cheers !

    Excel Range to BBCode Table
    Use Social Networking Tools If You Like the Answers !

    Message to Cross Posters

    @ Home - Office 2010/2013/2016 on Win 10 (64 bit); @ Work - Office 2016 on Win 10 (64 bit)

  4. #4
    Junior Member
    Join Date
    Sep 2011
    Posts
    13
    Rep Power
    0
    Sir, It is really excellent !!!!!!!!!!! Thanks a million !!!! The formula is very much useful which will same much of my time,

    Thanks very much.........

    This forum is really good as i have become a freequent visitor. The solutions are received quickly . Thanks for the quick response.

    Regards
    R.Ramarishnan

  5. #5
    Administrator Admin's Avatar
    Join Date
    Mar 2011
    Posts
    1,122
    Rep Power
    10
    Thanks for the feedback
    Cheers !

    Excel Range to BBCode Table
    Use Social Networking Tools If You Like the Answers !

    Message to Cross Posters

    @ Home - Office 2010/2013/2016 on Win 10 (64 bit); @ Work - Office 2016 on Win 10 (64 bit)

Similar Threads

  1. Replies: 7
    Last Post: 05-15-2013, 02:56 PM
  2. Replies: 16
    Last Post: 04-19-2013, 08:20 PM
  3. Replies: 4
    Last Post: 08-11-2012, 10:50 PM
  4. Lookup Based on Criteria
    By maruthi in forum Excel Help
    Replies: 2
    Last Post: 02-15-2012, 08:49 AM
  5. Count with Multiple Criteria
    By candygirl in forum Excel Help
    Replies: 3
    Last Post: 12-11-2011, 07:02 PM

Tags for this Thread

Posting Permissions

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