Use LARGE function with non-numerical data

[Rainmanne]

I need to pick up the lowest value out of two highest out of three. So I have now 3 non-numerical values, which correspond to numerical values and I need to choose the lowest out of the two highest. I figured out that I can use LARGE to pick the 2 highest values and then use MIN to pick up the lowest out of them. However, LARGE takes an array and I cannot figure out how to use non-numerical array with LARGE.

I have created a Scale sheet in the workbook, which sets the relationship between various sets of non-numerical grades and assigns numerical value to each of them. And I basically use INDEX/MATCH to convert non-numerical into numerical if I need to manipulate them and then convert back to non-numerical.

Please see below the setup I have:

Scale sheet

Non-Num	Num	Grade Set 1	Grade Set 2/3
	25
1	24	A	Up+
1a	23	B	Up
1b	22	C	Up-

Calc sheet

Item	Grade Set 1	Grade Set 2	Grade Set 3	Set 1 in Non-Num	Set 2 in Non-Num	Set 3 in Non-Num	Lowest grade
XYZ	A	Up	Up-	1	1a	1b	? (should be 1a)

So I put grades from sets 1, 2 and 3 in columns 2-4 and convert them into unified Non-Num grades in cols 5-7. In column 8 I want to display the lowest grade of two highest in cols 5-7. So I tried

Excel Formula:
=MIN(INDEX(Scale!$A$1:$D$26,MATCH(LARGE(J6:L6,1),Scale!$A$1:$A$26,0),2),IFERROR(INDEX(Scale!$A$1:$D$26,MATCH(LARGE(J6:L6,2),Scale!$A$1:$A$26,0),2),25))

but t doesn't work because content of the arrays for the LARGE function are non-numeric. I can of course create a three separate columns, convert non-numericals into numericals but I would like to avoid it and rather do it in col 8. Sorry if it's all presented in a confusing way.

 

[jasonb75]

See if this works, you might need to enter it as an array formula.

=IFERROR(INDEX(Scale!$A$1:$A$26,AGGREGATE(14,6,MATCH(J6:L6,Scale!$A$1:$A$26,0),2)),IFERROR(INDEX(J6:L6,MATCH(2,1/LEN(J6:L6))),""))

 

[Rainmanne]

See if this works, you might need to enter it as an array formula.

=IFERROR(INDEX(Scale!$A$1:$A$26,AGGREGATE(14,6,MATCH(J6:L6,Scale!$A$1:$A$26,0),2)),IFERROR(INDEX(J6:L6,MATCH(2,1/LEN(J6:L6))),""))

Thanks a lot! It works perfectly!