matching multiple criteria and output result to worksheet

75VHSAlum

New member
Joined
Sep 18, 2013
Messages
7
Reaction score
0
Points
0
:confused2: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.
 

Attachments

  • Co-op database(1).xlsx
    61.3 KB · Views: 22

NBVC

Super Moderator
Staff member
Joined
May 20, 2011
Messages
1,518
Reaction score
0
Points
0
Location
Mississauga, Canada
Excel Version(s)
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....
 

75VHSAlum

New member
Joined
Sep 18, 2013
Messages
7
Reaction score
0
Points
0
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.
 

Attachments

  • Co-op database(1).xlsx
    61.3 KB · Views: 18

Bob Phillips

Super Moderator
Staff member
Joined
Mar 21, 2011
Messages
1,942
Reaction score
0
Points
36
Excel Version(s)
O365
You need to array-enter them (Ctrl-Shift-Enter)
 

75VHSAlum

New member
Joined
Sep 18, 2013
Messages
7
Reaction score
0
Points
0
Thanks - I keep forgetting to do that. Learning something new every day.
 
Top