VLOOKUP or INDEX Match for Grades based on Team

How about this:

Please Login or Register  to view this content.

Cheers,

A quick way to combine all three would be a nested IF

=IF(X1="LCAD",VLOOKUP(Y1,$X$3:$AA$6,4))
=IF(X1="AI",VLOOKUP(Y1,$Y$3:$AA$6,3))
=IF(X1="FW",VLOOKUP(Y1,$Z$3:$AA$6,2))

=IF(X1="LCAD",VLOOKUP(Y1,$X$3:$AA$6,4) , IF(X1="AI",VLOOKUP(Y1,$Y$3:$AA$6,3) , IF(X1="FW",VLOOKUP(Y1,$Z$3:$AA$6,2) ."" )))

An explanation for help with your proficiency! :D

=INDEX(AA4:AA6, MATCH(Y1, INDEX(X3:Z6,0,MATCH(X1,X2:Z2,0)), 1), 1)

The way this works :
INDEX( Array , Row #, Column #)

So you know you are looking at the letter grade column "AA4:AA6" but you want to know which one to pick.
Because this is only a single column we can either put "1" or "0" or nothing in the last argument of INDEX (column #).
Thus so far we have:
INDEX(AA4:AA6 , Row #, 1 )
Now we need the row number, this is the fun part.

MATCH(Value, ARRAY, [Match Type])
This amazing formula allows us to determine the place of any entered Value within an array. The [Match Type] can be used to say, we would like to find an exact match for the Value in the ARRAY (fill in a 0)
or we would like to find the largest value less than or equal to our Value (fill in a 1)
or the smallest value greater than or equal to our Value (fill in a -1).
Here we will choose a "1" meaning choose the largest value in the range that is less than our value entered, which makes sense since we haven't passed the cut off for getting the higher grade!
The Value you have generated is in Y1 so we put that badboy in.
MATCH(Y1 , ARRAY , 1)
So far our main formula is now:
INDEX(AA4:AA6 , MATCH(Y1, ARRAY, 1) , 1 )

FINDING THE ARRAY!
So what column are we matching Y1 in? Surely X1 will change "LCAD" to other metrics "AI" or "FW" as you would like so we need a way of selecting a column based on X1. What column is LCAD in? ENTER ANOTHER MATCH FORMULA!
Find the column # from X2 to Z2 that equals exactly X1!
MATCH(X1, X2:Z2 , 0)
Beautiful.
Now we will get a "1" from this if you have "LCAD" written in X1.
A "2" if you have "AI" written in X1.
Or a "3" if you have "FW" written in X1.

This next step is where Conne did the real magic and stepped out of the mortal realm of INDEX MATCH: INDEX-MATCH-CEPTION.
First, we take the match function we just created that chooses the correct column number and throw it into an INDEX function of the table of number grades (X4:Z6).
This says look at the 3x3 table (X4:Z6) and focus on whichever column the MATCH function picks (1, 2, or 3).
INDEX(X4:Z6, 0,MATCH(X1, X2:Z2 , 0))
Not too intuitively, the result of this function actuallyis changing from X4:X6 -> Y4:Y6 -> Z:Z6 depending on if we have "LCAD", "AI", or "FW".

Stick this into the main formula's MATCH's ARRAY so we can properly look up values from the correct column.

INDEX(AA4:AA6 , MATCH(Y1, ARRAY, 1) , 1 )
becomes
INDEX(AA4:AA6 , MATCH(Y1, INDEX(X4:Z6, 0, MATCH(X1, X2:Z2 , 0)), 1) , 1 )
Which is our final formula.

INDEX( Array , Row #, Column #)
INDEX(Letter Grades, (The Row # of a Grade number in a column (selected from 3 choices of columns based on the text in X1) that is less than the generated number in Y1) , Letter Grades is only 1 column wide)

I hope this was helpful and didn't absolutely confuse you!
Red