lookup logic to interrogate specific exceptions - part 2

[bdav1216]

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

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>

 

[Marcelo Branco]

Maybe...

C2 copied down
=IF(AND(A2<>"",B2<>"NONE"),INDEX(IF(ISNUMBER(MATCH(B2,Sheet2!$A$7:$A$9,0)),Sheet2!$B$2:$B$5,IF(ISNUMBER(MATCH(B2,Sheet2!$D$7:$D$9,0)),Sheet2!$E$2:$E$5)),MATCH(A2,Sheet2!$A$2:$A$5,0)),"-")

M.

 

[bdav1216]

Maybe...

C2 copied down
=IF(AND(A2<>"",B2<>"NONE"),INDEX(IF(ISNUMBER(MATCH(B2,Sheet2!$A$7:$A$9,0)),Sheet2!$B$2:$B$5,IF(ISNUMBER(MATCH(B2,Sheet2!$D$7:$D$9,0)),Sheet2!$E$2:$E$5)),MATCH(A2,Sheet2!$A$2:$A$5,0)),"-")


M.

Thank you!
This does appear to be working, but the names (a7-a9 and b7-b9 in sheet2) won't actually be present in my real data - that was just for this example.
How could this be updated to refer the lookup to the correct table based on the names (Brian, Mike or Shawn) would go the values Column D / E of Sheet2 and Julie, Sharon or Liz would go to Column A/B of Sheet 2.

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.

 

[Marcelo Branco]

Maybe...

=IF(AND(A2<>"",B2<>"NONE"),INDEX(IF(OR(B2={"Brian";"Mike";"Shawn"}),Sheet2!$E$2:$E$5,IF(OR(B2={"Julie";"Sharon";"Liz"}),Sheet2!$B$2:$B$5)),MATCH(A2,Sheet2!$A$2:$A$5,0)),"-")

M.

 

[bdav1216]

Maybe...

=IF(AND(A2<>"",B2<>"NONE"),INDEX(IF(OR(B2={"Brian";"Mike";"Shawn"}),Sheet2!$E$2:$E$5,IF(OR(B2={"Julie";"Sharon";"Liz"}),Sheet2!$B$2:$B$5)),MATCH(A2,Sheet2!$A$2:$A$5,0)),"-")

M.

Very nice! It appears to be functionally working now on my data, just need to review.

One minor update:

How would I add one additional check within the first part of that statement to say if B2 was equal to "Steve" also output a '-' value (in addition to what is already present).

=IF(AND(A2<>"",B2<>"NONE"),INDEX(IF(OR(B2={"Brian";"Mike";"Shawn"}),Sheet2!$E$2:$E$5,IF(OR(B2={"Julie";"Sharon";"Liz"}),Sheet2!$B$2:$B$5)),MATCH(A2,Sheet2!$A$2:$A$5,0)),"-")

 

[Marcelo Branco]

=IF(AND(A2<>"",B2<>"NONE",B2<>"Steve"),INDEX(.....),"-")

M.

 

[bdav1216]

Thank you! I will review and provide any additional feedback if needed. Much appreciated!

 

[Marcelo Branco]

You are welcome. Thanks for the feedback.

M.