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
[TABLE="width: 255"]
<colgroup><col><col><col></colgroup><tbody>[TR]
[TD]Grade[/TD]
[TD]Manager[/TD]
[TD]Expected Results[/TD]
[/TR]
[TR]
[TD]A01[/TD]
[TD]Brian[/TD]
[TD="align: right"]30[/TD]
[/TR]
[TR]
[TD]A02[/TD]
[TD]Julie[/TD]
[TD="align: right"]20[/TD]
[/TR]
[TR]
[TD]A03[/TD]
[TD]Brian[/TD]
[TD="align: right"]80[/TD]
[/TR]
[TR]
[TD]A04[/TD]
[TD]Julie[/TD]
[TD="align: right"]50[/TD]
[/TR]
[TR]
[TD]A02[/TD]
[TD]Mike[/TD]
[TD="align: right"]60[/TD]
[/TR]
[TR]
[TD]A04[/TD]
[TD]Sharon[/TD]
[TD="align: right"]50[/TD]
[/TR]
[TR]
[TD]A04[/TD]
[TD]Mike[/TD]
[TD="align: right"]90[/TD]
[/TR]
[TR]
[TD]A02[/TD]
[TD]Shawn[/TD]
[TD="align: right"]90[/TD]
[/TR]
[TR]
[TD]A03[/TD]
[TD]Liz[/TD]
[TD="align: right"]40[/TD]
[/TR]
[TR]
[TD]blank value[/TD]
[TD][/TD]
[TD]-[/TD]
[/TR]
[TR]
[TD]A01[/TD]
[TD]NONE[/TD]
[TD]-[/TD]
[/TR]
</tbody>[/TABLE]


Sheet 2

Col A Col B Col D Col E
[TABLE="width: 320"]
<colgroup><col width="64" span="5" style="width:48pt"> </colgroup><tbody>[TR]
[TD="class: xl66, width: 64"]GRADE[/TD]
[TD="class: xl67, width: 64"]Average[/TD]
[TD="width: 64"][/TD]
[TD="class: xl66, width: 64"]GRADE[/TD]
[TD="class: xl67, width: 64"]Average[/TD]
[/TR]
[TR]
[TD="class: xl68"]A01[/TD]
[TD="class: xl69, align: right"]10.0[/TD]
[TD][/TD]
[TD="class: xl68"]P01[/TD]
[TD="class: xl69, align: right"]30.0[/TD]
[/TR]
[TR]
[TD="class: xl68"]A02[/TD]
[TD="class: xl69, align: right"]20.0[/TD]
[TD][/TD]
[TD="class: xl68"]P02[/TD]
[TD="class: xl69, align: right"]60.0[/TD]
[/TR]
[TR]
[TD="class: xl68"]A03[/TD]
[TD="class: xl69, align: right"]40.0[/TD]
[TD][/TD]
[TD="class: xl68"]P03[/TD]
[TD="class: xl69, align: right"]80.0[/TD]
[/TR]
[TR]
[TD="class: xl68"]A04[/TD]
[TD="class: xl69, align: right"]50.0[/TD]
[TD][/TD]
[TD="class: xl68"]P04[/TD]
[TD="class: xl69, align: right"]90.0[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]juli[/TD]
[TD][/TD]
[TD][/TD]
[TD]shawn[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]sharon[/TD]
[TD][/TD]
[TD][/TD]
[TD]mike[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]liz[/TD]
[TD][/TD]
[TD][/TD]
[TD]Brian[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[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.