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]

Large is a numeric function, it will never work with anything non-numeric, the same applies to Min and many other functions.

The best thing that you could do is list all grades in ascending order (which I assume you have done in A1:A26) then use something like

=INDEX(Scale!$A$1:$A$26,LARGE(MATCH(J6:L6,Scale!$A$1:$A$26,0),2))

 

[Rainmanne]

Large is a numeric function, it will never work with anything non-numeric, the same applies to Min and many other functions.

The best thing that you could do is list all grades in ascending order (which I assume you have done in A1:A26) then use something like =INDEX(Scale!$A$1:$A$26,LARGE(MATCH(J6:L6,Scale!$A$1:$A$26,0),2))

Thanks a lot, but unfortunately it returns #VALUE!. I tried it as an array formula but it returns a wrong value. I cannot figure out how to convert a range of non-numerical values into numerical for LARGE in an array formula. For example, I tired just

{=MATCH(J10:L10,Scale!$A$1:$A$26,0)}

and it returns a number 6, while the values in J10:L10 correspond to 21, 17 and 7. And if I add LARGE:

{=LARGE(MATCH(J10:L10,Scale!$A$1:$A$26,0),1)}

it returns 20

I cannot understand how it works. Array functions always baffled me.

 

[jasonb75]

I tried it as an array formula but it returns a wrong value.

Then it sounds like it's the right formula but might need a bit of adjustment. What happens if you test it on several rows of data, can you see any pattern to the wrong results, for example is it always 1 away from where it should be?

 

[Rainmanne]

Then it sounds like it's the right formula but might need a bit of adjustment. What happens if you test it on several rows of data, can you see any pattern to the wrong results, for example is it always 1 away from where it should be?

I have finally managed to install XL2BB. So posting the range.

mre.xlsx
	A	B	C	D	E	F	G	H	I	J
1	Item	Non-Num	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	Should Be
2	Item1		A	Mid+	Up	1	3	1a	10	1a
3	Item2		B	Mid	Up-	1a	3a	1b	9	1b
4	Item3		C	Mid-	LUP+	1b	3b	2	8	2
5	Item4		D	Down+	LUP	2	4	2a	7	2a
6	Item5		E	Down	LUP-	2a	4a	2b	6b	2b
7	Item6		F	Down-	Down+	2b	4b	4	5a	4
8	Item7		G	LDown+	Down	3	5	4a	5	4a
9	Item8		L	LDown	Down-	4b	5a	4b	4b	4b
10	Item9		M	Up+	LDown+	5	1	5	4a	5
11	Item10
12	Item11
13	Item12
14	Item13
15	Item14
16	Item15
17	Item16
18	Item17
19	Item18
20	Item19
21	Item20
22	Item21
23	Right-click and insert a new row here and fill down columns A-K
Rating

Cell Formulas
Range	Formula
F2:F10	F2	=IF(ISBLANK(C2),"",INDEX(Scale!$A$1:$D$26,MATCH(C2,Scale!$C$1:$C$26,0),MATCH($B$1,Scale!$A$1:$D$1,0)))
G2:H10	G2	=IF(ISBLANK(D2),"",INDEX(Scale!$A$1:$D$26,MATCH(D2,Scale!$D$1:$D$26,0),MATCH($B$1,Scale!$A$1:$D$1,0)))
I2:I10	I2	=INDEX(Scale!$A$2:$D$26,MATCH(MIN(LARGE(MATCH(F2:H2,Scale!$A$2:$A$26,0),1),LARGE(MATCH(F2:H2,Scale!$A$2:$A$26,0),2)),Scale!$B$2:$B$26,0),1)
F11:F22	F11	=IF(ISBLANK(C11),"",INDEX(Scale!$A$1:$D$26,MATCH(C11,Scale!$C$1:$C$26,0),MATCH(#REF!,Scale!$A$1:$D$1,0)))
G11:H22	G11	=IF(ISBLANK(D11),"",INDEX(Scale!$A$1:$D$26,MATCH(D11,Scale!$D$1:$D$26,0),MATCH(#REF!,Scale!$A$1:$D$1,0)))
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[Rainmanne]

Not sure how to post both sheets

 

[jasonb75]

To post a second sheet you can go back to excel and copy it with XL2BB the same way as you did for the first one, then paste it below the BB code for the first sheet.

From the example provided, the only reason that I can see for the wrong results is that you are using the wrong formula. The formula in your sheet is not the one that I suggested.

 

[Rainmanne]

To post a second sheet you can go back to excel and copy it with XL2BB the same way as you did for the first one, then paste it below the BB code for the first sheet.

From the example provided, the only reason that I can see for the wrong results is that you are using the wrong formula. The formula in your sheet is not the one that I suggested.

Sorry I have messed up the formula. It works now! I was a bit stupid because the condition I had was "the lowest of the two highest values" and I only now realised that it's the same as "the second largest" as your formula does.

The only issue I have now is that the formula only works, when all three values are present. If there are only two or one value, it returns #N/A. I guess it's because a blank cell with a formula does not have a match in the Scale table. I don't know how to edit a published post so I am posting the Scale sheet here in case it's needed.

mre.xlsx
	A	B	C	D
1	Non-Num	Num	Grade Set 1	Grade Set 2/3
2	0	25
3	1	24	A	Up+
4	1a	23	B	Up
5	1b	22	C	Up-
6	2	21	D	LUP+
7	2a	20	E	LUP
8	2b	19	F	LUP-
9	3	18	G	Mid+
10	3a	17	H	Mid
11	3b	16	I	Mid-
12	4	15	J	Down+
13	4a	14	K	Down
14	4b	13	L	Down-
15	5	12	M	LDown+
16	5a	11	NA	LDown
17	5b	10	O	LDown-
18	6	9	P	BT+
19	6a	8	Q	BT
20	6b	7	R	BT-
21	7	6	S	BT--
22	8	5	S-X	LL
23	9	4	NA	NA
24	10	3	NA	NA
25	11	2	NA	NA
26	NR	1	NA	NA
Scale

 

[jasonb75]

The only issue I have now is that the formula only works, when all three values are present. If there are only two or one value, it returns #N/A.

What would you expect if there is only one value in the row, should it show that as the result or just show nothing?

This will work as long as there are two or more values that can be matched, but will need some additional changes depending on the requirement for a single value.

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

 

[Rainmanne]

What would you expect if there is only one value in the row, should it show that as the result or just show nothing?

This will work as long as there are two or more values that can be matched, but will need some additional changes depending on the requirement for a single value.

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

If there's only one value, it should be displayed in the results. If the whole row is blank then it should show an empty cell, which I can probably do with IFNA function.