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
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]name[/TD]
[TD]safety training[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]John[/TD]
[TD]x[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

Worksheet 2
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Name[/TD]
[TD]Training[/TD]
[TD]Result
[/TD]
[/TR]
[TR]
[TD]John[/TD]
[TD]safety training[/TD]
[TD]pass[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

I would appreciate all the help.


Thanks!

 

[Logit]

.
If your data on Sheet 1 begins in A1 :


[Table="width:, class:head"][tr=bgcolor:#888888][th] [/th][th]

A​

[/th][th]

B​

[/th][th]

C​

[/th][/tr]
[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]

1​

[/td][td]name[/td][td]safety training[/td][td][/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]

2​

[/td][td]John[/td][td]X[/td][td][/td][/tr]
[/table]
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

[TABLE="class: grid, width: 500, align: left"]
<tbody>[TR]
[TD]name[/TD]
[TD]Training 1[/TD]
[TD]Training 2[/TD]
[TD]Training 3[/TD]
[TD]Training 4[/TD]
[/TR]
[TR]
[TD]John[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Jane[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Matt[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Dan[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]










Sheet 2 =Training records

[TABLE="class: grid, width: 500, align: left"]
<tbody>[TR]
[TD]name[/TD]
[TD]training title[/TD]
[TD]result[/TD]
[/TR]
[TR]
[TD]Matt
[/TD]
[TD]Training 1[/TD]
[TD]Pass[/TD]
[/TR]
[TR]
[TD]Eric[/TD]
[TD]Training 1[/TD]
[TD]Pass[/TD]
[/TR]
[TR]
[TD]Jane[/TD]
[TD]Training 1[/TD]
[TD]Mandatory[/TD]
[/TR]
[TR]
[TD]Todd[/TD]
[TD]Training 1[/TD]
[TD]Pass[/TD]
[/TR]
[TR]
[TD]Dan[/TD]
[TD]Training 1[/TD]
[TD]Mandatory[/TD]
[/TR]
[TR]
[TD]John[/TD]
[TD]Training 1[/TD]
[TD]Pass[/TD]
[/TR]
[TR]
[TD]Matt[/TD]
[TD]Training 2[/TD]
[TD]Mandatory[/TD]
[/TR]
[TR]
[TD]Dan[/TD]
[TD]Training 2[/TD]
[TD]Pass[/TD]
[/TR]
[TR]
[TD]John[/TD]
[TD]Training 2[/TD]
[TD]Pass[/TD]
[/TR]
</tbody>[/TABLE]

****** id="cke_pastebin" style="position: absolute; top: 454px; width: 1px; height: 1px; overflow: hidden; left: -1000px;">[TABLE="class: grid, width: 500, align: left"]
<tbody>[TR]
[TD]Training 1[/TD]
[/TR]
</tbody>[/TABLE]
</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!