Need Help Writing Excel Formual

[GALibra109]

Hello! Excel semi-newbie here. I need help with a problem I've been tasked to help solve in Excel and it involves figuring out what functions to use to create a formula to pull out the information I need.

Sheet 1 = Candidate Information
This table has 3 columns: Candidate ID, Zip Code and EmpPref

Sheet 2 = Job Order Info
This table also has 3 columns: Job Order ID, Zip Code and EmpPref

I'd like to create a third table that combines the information from the 2 tables. I'd like the third table to have these three columns: Candidate ID, # of Job Order Matches, and Job Order IDs.

I'm having a hard time figuring out how to write a formula (which functions to use) to pull the Job Orders for the correct matching candidates based on Zip Code and EmpPref.

Any help is appreciated!

Thanks!
GALibra109

 

[Eric W]

Welcome to the Board.

It's a little hard to picture what you want. The old adage that a picture is worth a thousand words definitely applies here. But here's what I surmised:

If your first sheet looks like this:

	A	B	C
Candidate ID	ZIP Code	EmpPref
a
b
c
a
b
d
e
f
z
m
g

<colgroup><col style="width: 25pxpx"><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]

[TD="align: center"]2[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]12345[/TD]

[TD="align: center"]3[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]12345[/TD]

[TD="align: center"]4[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]12345[/TD]

[TD="align: center"]5[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]23456[/TD]

[TD="align: center"]6[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]23456[/TD]

[TD="align: center"]7[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]34567[/TD]

[TD="align: center"]8[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]12345[/TD]

[TD="align: center"]9[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]12345[/TD]

[TD="align: center"]10[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]23456[/TD]

[TD="align: center"]11[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]34567[/TD]

[TD="align: center"]12[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]12345[/TD]

</tbody>

Sheet1​

Then your second sheet could look like:

	A	B	C	D	E	F	G	H	I
Job Order ID	ZIP Code	EmpPref	Candidate ID	# of Job Order Matches	Job Order ID
m
c
a
a
a
q
z
b
r

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]

[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]2[/TD]
[TD="align: right"]111[/TD]
[TD="align: right"]34567[/TD]

[TD="align: right"][/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]222[/TD]
[TD="align: right"]555[/TD]
[TD="align: right"]888[/TD]

[TD="align: center"]3[/TD]
[TD="align: right"]222[/TD]
[TD="align: right"]12345[/TD]

[TD="align: right"][/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]333[/TD]
[TD="align: right"]444[/TD]
[TD="align: right"]777[/TD]

[TD="align: center"]4[/TD]
[TD="align: right"]333[/TD]
[TD="align: right"]23456[/TD]

[TD="align: right"][/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]111[/TD]

[TD="align: center"]5[/TD]
[TD="align: right"]444[/TD]
[TD="align: right"]23456[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]6[/TD]
[TD="align: right"]555[/TD]
[TD="align: right"]12345[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]7[/TD]
[TD="align: right"]666[/TD]
[TD="align: right"]12345[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]8[/TD]
[TD="align: right"]777[/TD]
[TD="align: right"]23456[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]9[/TD]
[TD="align: right"]888[/TD]
[TD="align: right"]12345[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]10[/TD]
[TD="align: right"]999[/TD]
[TD="align: right"]34567[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]11[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>

Sheet2​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Array Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]F2[/TH]
[TD="align: left"]{=SUM(COUNTIFS(B:B,IF(Sheet1!$A$2:$A$12=E2,Sheet1!$B$2:$B$12,"zz"),C:C,IF(Sheet1!$A$2:$A$12=E2,Sheet1!$C$2:$C$12,"zz")))}[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]G2[/TH]
[TD="align: left"]{=IFERROR(INDEX($A$2:$A$10,SMALL(IF(ISNUMBER(MATCH($E2&"|"&$B$2:$B$10&"|"&$C$2:$C$10,Sheet1!$A$2:$A$12&"|"&Sheet1!$B$2:$B$12&"|"&Sheet1!$C$2:$C$12,0)),ROW($A$2:$A$10)-ROW($A$2)+1),COLUMNS($G2:G2))),"")}[/TD]
[/TR]
</tbody>[/TABLE]
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself[/TD]
[/TR]
</tbody>[/TABLE]



Put the formula in F2, confirm by pressing Control+Shift+Enter, then drag the formula down the column. Repeat with the formula in G2, but copy it down and to the right as needed.

Let us know if this works for you.

 

[GALibra109]

Eric, thank you so much for your help and quick response. Because I wasn't 100% clear about what my tables looked like, your answer is a little off for what I was looking for, but you DEFINITELY gave me a great starting point! Thank you...I appreciate your help!

GALibra109