# Use LARGE function with non-numerical data

#### Rainmanne

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

##### Well-known Member
See if this works, you might need to enter it as an array formula.
Excel 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))),""))``

### Excel Facts

Does the VLOOKUP table have to be sorted?
No! when you are using an exact match, the VLOOKUP table can be in any order. Best-selling items at the top is actually the best.

#### Rainmanne

##### Board Regular
See if this works, you might need to enter it as an array formula.
Excel 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!

Replies
0
Views
151
Replies
26
Views
470
Replies
1
Views
113
Replies
0
Views
195
Replies
24
Views
759

1,187,189
Messages
5,962,116
Members
438,586
Latest member
flickalok

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back