Need help writing a complex formula

[Ronna Marcus]

Hello gurus!  I need help in trying to figure out which formulas to use and how to write them to solve this Excel problem.

Sheet 1 = Candidate Info

This table has 3 columns: Candidate ID, Zip Code, and EmpPref

Sheet 2 = Job Order Info

This table has 3 columns: Job Order ID, Zip Code, and Emp Pref

I would like to create Sheet 3 which combines some of the information from the other 2 tables.  This third table would have 3 columns: Candidate ID, # Job Order Matches, and Job Order IDs.  I am having trouble figuring out how to write a formula that will match the candidate info to the correct corresponding job orders.

I've included my examples to make it easier to understand what I'm trying to do.

Thanks for the help!

Ronna

[#2]

Hi Ronna,

If return the string when it could be array formula with TEXTJOIN

=TEXTJOIN(", ",TRUE,IF(($B$9:$B$28=$B2)*($D$9:$D$28=$D2),$A$9:$A$28,""))

and for counting

=COUNTIFS($B$9:$B$28,INDEX($B$2:$B$6,MATCH($F19,$A$2:$A$6,0)),$D$9:$D$28,INDEX($D$2:$D$6,MATCH($F19,$A$2:$A$6,0)))

Please see third table under your samples in first sheet

[#3]

Sergei, this is wonderful!  I so appreciate your quick response and work in helping me find an answer!  I was so confused before, and now you've given me a great starting point.  

Thank you again!

Ronna  :0)

[#4]

Ronna, you are welcome