IF THEN formula help with multiple criteria (selections)

[kittenmeants]

Hello!

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

Current Layout:
Store Manager Name	Overall Performance (Can only be Red, Green, Yellow)	Career Mobility (Can only be Upward, Lateral, Well Placed)
X	Green	Upward
Y	Green	Upward
Z	Red	Well Placed
ZZ	Yellow	Well Placed

New Layout Needed:
Talent Tier	Overall Performance	Career Mobility
Top	Green	Upward
Critical	Green	Lateral or Well Placed
Core	Yellow	Lateral or Well Placed
Underperforming	Red	Well 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 · Accepted solution]

@kittenmeants 

I'd create a two-dimensional lookup range:

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

[#3]

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.