Use LARGE function with non-numerical data

Rainmanne

Board Regular
Joined
Mar 10, 2016
Messages
132
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.
 
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))),""))
 
Upvote 0
Solution

Excel Facts

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.
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!
 
Upvote 0

Forum statistics

Threads
1,222,046
Messages
6,163,588
Members
451,846
Latest member
ajk99

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
Back
Top