Forum Discussion
Need help writing a complex formula
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
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
Ronna, you are welcome
