Excel Formula help

[Calix21]

I need your help excel heroes in debunking this formula.
What's the formula if i want to automatically mark x in worksheet 1 if john's training result in worksheet 2 is pass

Worksheet 1

name	safety training
John	x

<tbody>
</tbody>

Worksheet 2

Name	Training	Result
John	safety training	pass

<tbody>
</tbody>

I would appreciate all the help.


Thanks!

 

[Logit]

.
If your data on Sheet 1 begins in A1 :

	A​	B​	C​
1​	name	safety training
2​	John	X

Paste this formula in B2 :

=IF(Sheet2!C2="Pass","X", "")

Then you can drag it down the column as far as needed.

 

[Calix21]

Thanks this will only work for one person. Here's what I really need:
I have multiple trainees and training and I need my dashboard on sheet 1 to automatically populate the corresponding cell depending on what is in sheet 2 under their name and the specific training

Sheet 1=Dashboard
x=pass
m=mandatory

name	Training 1	Training 2	Training 3	Training 4
John
Jane
Matt
Dan

<tbody>
</tbody>

Sheet 2 =Training records

name	training title	result
Matt	Training 1	Pass
Eric	Training 1	Pass
Jane	Training 1	Mandatory
Todd	Training 1	Pass
Dan	Training 1	Mandatory
John	Training 1	Pass
Matt	Training 2	Mandatory
Dan	Training 2	Pass
John	Training 2	Pass

<tbody>
</tbody>

****** id="cke_pastebin" style="position: absolute; top: 454px; width: 1px; height: 1px; overflow: hidden; left: -1000px;">

Training 1

<tbody>
</tbody>

</body>

 

[lrobbo314]

This formula will return whatever value you have as a result. It can be modified if you just want it to show if they passed. This is an array formula so hit ctrl+shift+enter after pasting the formula.

=IFERROR(INDEX(Sheet2!$C$2:$C$10,MATCH(Sheet1!$A2&Sheet1!B$1,Sheet2!$A$2:$A$10&Sheet2!$B$2:$B$10,0)),"")

 

[admiral100]

Hi,

And this one if you must to convert pass = X & mandatory = M

Ctrl+Shift+Enter Not Just Ebter
=IFERROR(IF(INDEX(Sheet2!$C$2:$C$10,MATCH(1,IF(Sheet2!$A$2:$A$10=$A2,IF(Sheet2!$B$2:$B$10=B$1,1)),0))="Pass","X","M"),"")

 

[Calix21]

Thanks!

 

[Calix21]

Thanks guys! You are a lifesaver. Real life superheroes!