SOLVED

IF THEN formula help with multiple criteria (selections)

Brass Contributor

Hello!

 

I need support in creating a formula(s) that satisfy these requirements:

 

Current Layout:
Store Manager NameOverall Performance (Can only be Red, Green, Yellow)

Career Mobility (Can only be Upward, Lateral, Well Placed)

XGreenUpward
YGreenUpward
ZRedWell Placed
ZZYellowWell Placed

 

New Layout Needed:
Talent TierOverall PerformanceCareer Mobility
TopGreenUpward
CriticalGreen
Lateral or Well Placed
CoreYellow
Lateral or Well Placed
UnderperformingRedWell Placed

 

I'm currently at =IF(AND(H2="Green",J2="Upward"),"Top","")

 

I wasn't sure how to make that a simple formula with all the requirements needed. 

 

Thanks!

2 Replies
best response confirmed by kittenmeants (Brass Contributor)
Solution

@kittenmeants 

I'd create a two-dimensional lookup range:

HansVogelaar_0-1696262279744.png

Here it's on the same sheet, but it can be on another sheet, even a hidden one.

The formula in D2 can then be

=INDEX($I$3:$K$5,MATCH(B2,$H$3:$H$5,0),MATCH(C2,$I$2:$K$2,0))

HansVogelaar_1-1696262363520.png

Thank you so much! I am not great at INDEX formulas so that was a little tricky, but did the job and made it wayyyy easier than me creating a million formulas.
1 best response

Accepted Solutions
best response confirmed by kittenmeants (Brass Contributor)
Solution

@kittenmeants 

I'd create a two-dimensional lookup range:

HansVogelaar_0-1696262279744.png

Here it's on the same sheet, but it can be on another sheet, even a hidden one.

The formula in D2 can then be

=INDEX($I$3:$K$5,MATCH(B2,$H$3:$H$5,0),MATCH(C2,$I$2:$K$2,0))

HansVogelaar_1-1696262363520.png

View solution in original post