lookup logic to interrogate specific exceptions - part 2

bdav1216

New Member
Joined
Mar 19, 2016
Messages
24
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
GradeManagerExpected Results
A01Brian30
A02Julie20
A03Brian80
A04Julie50
A02Mike60
A04Sharon50
A04Mike90
A02Shawn90
A03Liz40
blank value-
A01NONE-

<colgroup><col><col><col></colgroup><tbody>
</tbody>


Sheet 2

Col A Col B Col D Col E
GRADEAverageGRADEAverage
A0110.0P0130.0
A0220.0P0260.0
A0340.0P0380.0
A0450.0P0490.0
julishawn
sharonmike
lizBrian

<colgroup><col width="64" span="5" style="width:48pt"> </colgroup><tbody>
</tbody>
 

Some videos you may like

Excel Facts

How to change case of text in Excel?
Use =UPPER() for upper case, =LOWER() for lower case, and =PROPER() for proper case. PROPER won't capitalize second c in Mccartney

Marcelo Branco

MrExcel MVP
Joined
Aug 23, 2010
Messages
16,393
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

New Member
Joined
Mar 19, 2016
Messages
24
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

MrExcel MVP
Joined
Aug 23, 2010
Messages
16,393
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

New Member
Joined
Mar 19, 2016
Messages
24

ADVERTISEMENT

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)),"-")
 

bdav1216

New Member
Joined
Mar 19, 2016
Messages
24
Thank you! I will review and provide any additional feedback if needed. Much appreciated!
 

Watch MrExcel Video

Forum statistics

Threads
1,108,732
Messages
5,524,513
Members
409,583
Latest member
RedHelp

This Week's Hot Topics

Top