+ Reply to Thread
Results 1 to 9 of 9

Lookup - multiple criteria

  1. #1
    Registered User
    Join Date
    09-04-2014
    Location
    World
    MS-Off Ver
    2010
    Posts
    29

    Lookup - multiple criteria

    Hi,

    Please help me with looking up using multiple criterias, see attached file.
    Thanks in advance.
    Attached Files Attached Files

  2. #2
    Forum Moderator davesexcel's Avatar
    Join Date
    02-19-2006
    Location
    Regina
    MS-Off Ver
    MS 365
    Posts
    13,264

    Re: Lookup - multiple criteria

    Could you explain more what you are trying to do, instead of just saying "here's my file, you figure it out"

  3. #3
    Registered User
    Join Date
    09-29-2015
    Location
    Essex, England
    MS-Off Ver
    2013
    Posts
    10

    Re: Lookup - multiple criteria

    You could either use a helper column on each spreadsheet, which concatenates the fields you are using in the lookup, such as:

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


    which you could paste in cell G2 on Data and drag down, and cell F2 on the sheet for each month (then drag down). You could then lookup with this formula:

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


    (paste under the Jan column on the Data sheet and drag down).

    Or, if you fancied an array formula (confirm by CTRL+SHIFT+ENTER, rather than ENTER), you could use this:

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

  4. #4
    Forum Guru samba_ravi's Avatar
    Join Date
    07-26-2011
    Location
    Hyderabad, India
    MS-Off Ver
    Excel 2013
    Posts
    8,574

    Re: Lookup - multiple criteria

    E2=IFERROR(LOOKUP(2,1/((Jan!$A$2:$A$7=$A2)*(Jan!$B$2:$B$7=$B2)*(Jan!$C$2:$C$7=$C2)*(Jan!$D$2:$D$7=$D2)),Jan!$E$2:$E$7),"")
    Please Login or Register  to view this content.
    F2=IFERROR(LOOKUP(2,1/((Feb!$A$2:$A$7=$A2)*(Feb!$B$2:$B$7=$B2)*(Feb!$C$2:$C$7=$C2)*(Feb!$D$2:$D$7=$D2)),Feb!$E$2:$E$7),"")
    Please Login or Register  to view this content.
    Try this and copy towards down
    Samba

    Say thanks to those who have helped you by clicking Add Reputation star.

  5. #5
    Forum Guru AlKey's Avatar
    Join Date
    07-20-2009
    Location
    Lakeland, FL USA
    MS-Off Ver
    Microsoft Office 2010/ Office 365
    Posts
    8,903

    Re: Lookup - multiple criteria

    For January

    in E2 and copy down

    =SUMPRODUCT((Jan!A$2:A$7=A2)*(Jan!B$2:B$7=B2)*(Jan!C$2:C$7=C2)*(Jan!D$2:D$7=D2)*(Jan!E$2:E$7))

    For February

    in F2 and copy down

    =SUMPRODUCT((Feb!A$2:A$7=A2)*(Feb!B$2:B$7=B2)*(Feb!C$2:C$7=C2)*(Feb!D$2:D$7=D2)*(Feb!E$2:E$7))
    If you like my answer please click on * Add Reputation
    Don't forget to mark threads as "Solved" if your problem has been resolved

    "Nothing is so firmly believed as what we least know."
    --Michel de Montaigne

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

    Re: Lookup - multiple criteria

    Quote Originally Posted by AlKey View Post
    For January

    in E2 and copy down

    =SUMPRODUCT((Jan!A$2:A$7=A2)*(Jan!B$2:B$7=B2)*(Jan!C$2:C$7=C2)*(Jan!D$2:D$7=D2)*(Jan!E$2:E$7))

    For February

    in F2 and copy down

    =SUMPRODUCT((Feb!A$2:A$7=A2)*(Feb!B$2:B$7=B2)*(Feb!C$2:C$7=C2)*(Feb!D$2:D$7=D2)*(Feb!E$2:E$7))
    Looks like good candidates for SUMIFS.
    Biff
    Microsoft MVP Excel
    Keep It Simple Stupid

    Let's Go Pens. We Want The Cup.

  7. #7
    Forum Guru AlKey's Avatar
    Join Date
    07-20-2009
    Location
    Lakeland, FL USA
    MS-Off Ver
    Microsoft Office 2010/ Office 365
    Posts
    8,903

    Re: Lookup - multiple criteria

    With SUMIFS

    =SUMIFS(Jan!E:E,Jan!A:A,A2,Jan!B:B,B2,Jan!C:C,C2,Jan!D:D,D2)

    and

    =SUMIFS(Feb!E:E,Feb!A:A,A2,Feb!B:B,B2,Feb!C:C,C2,Feb!D:D,D2)

  8. #8
    Forum Guru AlKey's Avatar
    Join Date
    07-20-2009
    Location
    Lakeland, FL USA
    MS-Off Ver
    Microsoft Office 2010/ Office 365
    Posts
    8,903

    Re: Lookup - multiple criteria

    Also if you change the headers for month in E1 and F1 to Jan and Feb you can use just one formula

    enter formula in E2, pull it to the right and then down

    =SUMIFS(INDIRECT("'"&E$1&"'!E:E"),INDIRECT("'"&E$1&"'!A:A"),$A2,INDIRECT("'"&E$1&"'!B:B"),$B2,INDIRECT("'"&E$1&"'!C:C"),$C2,INDIRECT("'"&E$1&"'!D:D"),$D2)

  9. #9
    Registered User
    Join Date
    09-04-2014
    Location
    World
    MS-Off Ver
    2010
    Posts
    29

    Re: Lookup - multiple criteria

    Thanks everyone, I will add to your reps. Though, I have not gotten a chance to try out your suggestions yet.
    @davesexcel: Sorry if my opening post lacked information. I thought it would be better to show what I am dealing with through a basic set-up in attached excel file, instead of trying to explain my issue.

+ 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. Replies: 1
    Last Post: 09-04-2014, 12:14 PM
  2. Replies: 9
    Last Post: 08-20-2014, 04:43 PM
  3. [SOLVED] Lookup and sum multiple values based on a single criteria in multiple sheets.
    By paulsanett in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 01-17-2014, 12:18 PM
  4. Replies: 0
    Last Post: 10-16-2013, 12:42 PM
  5. Replies: 11
    Last Post: 02-07-2013, 02:22 AM
  6. Replies: 2
    Last Post: 05-10-2012, 10:38 AM
  7. Replies: 1
    Last Post: 05-16-2011, 05:00 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