# lookup logic to interrogate specific exceptions - part 2

#### bdav1216

##### New Member
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>

### 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
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
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
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

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

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

M.

Last edited:

#### bdav1216

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

#### Marcelo Branco

##### MrExcel MVP
You are welcome. Thanks for the feedback.

M.

Replies
1
Views
264
Replies
1
Views
235
Replies
6
Views
376