Results 1 to 5 of 5

Thread: matching multiple criteria and output result to worksheet

  1. #1

    matching multiple criteria and output result to worksheet



    Register for a FREE account, and/
    or Log in to avoid these ads!

    Have a database set up listing our interns (Current Tab). It list their name, requisition number, manager's name, job title and what term (Spring, Summer, Winter). A requistion is good for usually 3 terms, but some are only for 1 term.
    I want to pull from my database to the form (Test Tab) the manager name, job title and requistion and then the name of the student for each term.
    I thought I had a solution since most requisitions are 3 terms but then I ran into problems when a requisition was for only 1 term. My formula would not work.
    This is an example of my formula. I am sure there is a better way.
    =IF(AND(Current!$E3<>Current!$E2,Current!$C3<>Current!$C2),Current!$E3,IF(AND(Current!$E4<>Current!$E3,Current!$C4<>Current!$C3),Current!$E4,IF(AND(Current!$E5<>Current!$E4,Current!$C5<>Current!$C4),Current!$E5,"")))

    Any help on how to do this would be appreciated.
    I have attached a sample of the worksheet.
    Attached Files Attached Files

  2. #2
    Super Moderator NBVC's Avatar
    Join Date
    May 2011
    Location
    Mississauga, Canada
    Posts
    1,429
    Articles
    0
    Excel Version
    Excel 2016
    In B3 enter:

    =IFERROR(INDEX(Current!$E$2:$E$41,MATCH(1,IF(Current!$C$2:$C$41<>"",IF(ISNA(MATCH(Current!$C$2:$C$41,$B$1:B2,0)),1)),0)),"")

    confirmed with CTRL+SHIFT+ENTER not just ENTER.

    in B4 enter:

    =IFERROR(INDEX(Current!$D$2:$D$41,MATCH(1,IF(Current!$C$2:$C$41<>"",IF(ISNA(MATCH(Current!$C$2:$C$41,$B$1:B3,0)),1)),0)),"")

    confirmed with CTRL+SHIFT+ENTER not just ENTER.

    in B5 enter:

    =IFERROR(INDEX(Current!$C$2:$C$41,MATCH(1,IF(Current!$C$2:$C$41<>"",IF(ISNA(MATCH(Current!$C$2:$C$41,$B$1:B4,0)),1)),0)),"")

    confirmed with CTRL+SHIFT+ENTER not just ENTER.

    Then copy B2:B4 and paste to B9, B15, B21, etc....


  3. #3
    Thank you, Thank you! It worked but now I am having issues with my forumulas on the Test Tab in columns c, e, g.
    Before these were working for me.
    Attached Files Attached Files

  4. #4
    You need to array-enter them (Ctrl-Shift-Enter)

  5. #5
    Thanks - I keep forgetting to do that. Learning something new every day.

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
  •