Use LARGE function with non-numerical data

Rainmanne

Board Regular
Joined
Mar 10, 2016
Messages
53
Office Version
  1. 2013
Platform
  1. Windows
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-NumNumGrade Set 1Grade Set 2/3
25
124AUp+
1a23BUp
1b22CUp-

Calc sheet
ItemGrade Set 1Grade Set 2Grade Set 3Set 1 in Non-NumSet 2 in Non-NumSet 3 in Non-NumLowest grade
XYZAUpUp-11a1b? (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
Joined
Dec 30, 2008
Messages
14,631
Office Version
  1. 365
Platform
  1. Windows
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))),""))
 
Solution

Excel Facts

What is the shortcut key for Format Selection?
Ctrl+1 (the number one) will open the Format dialog for whatever is selected.

Rainmanne

Board Regular
Joined
Mar 10, 2016
Messages
53
Office Version
  1. 2013
Platform
  1. Windows
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!
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,152,580
Messages
5,770,972
Members
425,654
Latest member
Kotika

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top