Hello,
If (Col B) Manager is equal to: Brian OR Mike OR Shawn then, using the grade field (Col A) output the corresponding average value associated in Sheet2 (Col D and E)
If (Col B) Manager is equal to: Julie OR Sharon OR Liz then, using the grade field (Col A), output the corresponding average value associated in Sheet2 (Col A and B).
If the Grade (Col A) value is blank OR Manager (Col B) is equal to NONE, then output a '-' in the expected value column.
Below I made examples to hopefully help
In Sheet2, under the look up values, I have the assoicated manager names listed.
Sheet 1:
Col A Col B Col C
<colgroup><col><col><col></colgroup><tbody>
</tbody>
Sheet 2
Col A Col B Col D Col E
<colgroup><col width="64" span="5" style="width:48pt"> </colgroup><tbody>
</tbody>
If (Col B) Manager is equal to: Brian OR Mike OR Shawn then, using the grade field (Col A) output the corresponding average value associated in Sheet2 (Col D and E)
If (Col B) Manager is equal to: Julie OR Sharon OR Liz then, using the grade field (Col A), output the corresponding average value associated in Sheet2 (Col A and B).
If the Grade (Col A) value is blank OR Manager (Col B) is equal to NONE, then output a '-' in the expected value column.
Below I made examples to hopefully help
In Sheet2, under the look up values, I have the assoicated manager names listed.
Sheet 1:
Col A Col B Col C
Grade | Manager | Expected Results |
A01 | Brian | 30 |
A02 | Julie | 20 |
A03 | Brian | 80 |
A04 | Julie | 50 |
A02 | Mike | 60 |
A04 | Sharon | 50 |
A04 | Mike | 90 |
A02 | Shawn | 90 |
A03 | Liz | 40 |
blank value | - | |
A01 | NONE | - |
<colgroup><col><col><col></colgroup><tbody>
</tbody>
Sheet 2
Col A Col B Col D Col E
GRADE | Average | GRADE | Average | |
A01 | 10.0 | P01 | 30.0 | |
A02 | 20.0 | P02 | 60.0 | |
A03 | 40.0 | P03 | 80.0 | |
A04 | 50.0 | P04 | 90.0 | |
juli | shawn | |||
sharon | mike | |||
liz | Brian |
<colgroup><col width="64" span="5" style="width:48pt"> </colgroup><tbody>
</tbody>