Returning yes/no if criteria matches horizontally and vertically

[nbressan]

Hi All, I have a sheet with names across the top and then training records for each person below their names. What function/formula do I need to use in order to return a yes/no value if a person has completed a particular training course?

Thanks in advance.

 

[ranman256]

Bob,swimming,
Bob, hiking, true
sally, hiking, true
etc...

put a true on the training completed,
then run a pivot table on the data to get your report.

 

[Eric W]

Welcome to the board.

Not sure what your layout is like. One way:

	A	B	C	D	E	F	G	H	I	J	K	L
1		Amy	Bob	Cindy	Dan	Erica	Fred		Name	Course	Taken?
2	Excel	x	x		x		x		Cindy	Word	Yes
3	Word		x	x
4	Powerpoint		x				x
5	Access		x			x

<tbody>
</tbody>

Sheet4​

Worksheet Formulas Cell Formula K2 =IF(INDEX($B$2:$G$5,MATCH(J2,$A$2:$A$5,0),MATCH(I2,$B$1:$G$1,0))="x","Yes","No") <tbody> </tbody>

<tbody>
</tbody>

Another:

	A	B	C	D	E	F	G	H	I	J	K	L
1		Amy	Bob	Cindy	Dan	Erica	Fred		Name	Course	Taken?
2		Excel	Excel	Word	Access	Word	Excel		Cindy	Word	Yes
3			Word			Access
4			Powerpoint			Excel
5			Access

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet4
​

Worksheet Formulas Cell Formula K2 =IF(COUNTIF(INDEX($B$2:$G$10,0,MATCH(I2,$B$1:$G$1,0)),J2),"Yes","No") <thead> </thead><tbody> </tbody>

<tbody>
</tbody>

 

[nbressan]

Hi Ranman256 and Eric W, thanks for your help so far.

Eric W, your second option was closer to what I need but when I update the formula to look at the cells I need, I end up with an #N/A. I have included a couple of screenshots examples of what my sheets look like.

Data Sheet

Report Sheet

 

[Eric W]

Assuming the upper left cell of your examples on each sheet is A1, put this formula in B2 on the Report sheet, and copy down and across as needed:

=IF(COUNTIF(INDEX(Data!$A$2:$Z$20,0,MATCH($A2,Data!$A$1:$Z$1,0)),B$1),"Yes","No")

Change the ranges as appropriate.