# matching multiple criteria and output result to worksheet

#### 75VHSAlum

##### New member
: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
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
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
You need to array-enter them (Ctrl-Shift-Enter)

#### 75VHSAlum

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