INDEX MATCH MAX multiple columns

[tmmcentyre]

I have four columns labelled Level 1, 2, 3, or 4. Each contain an Average Rating of BLANK to 100 derived from a ROUND & AVERAGEIF formula. The highest Rating & Level will be used to select a value from an INDEX table on Sheet2 and displayed as a Deduct Value. However, the INDEX table only goes up to 45 so any Rating >45 will need to select 45 at its Level. It also needs to show BLANK if all Ratings are BLANK.

Excel 2013/2016

	I	J	K	L	M
LEVEL
AVERAGE RATING
DEDUCT VALUE

<tbody>
[TD="align: center"]28[/TD]

[TD="align: center"]1[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]3[/TD]
[TD="align: center"]4[/TD]

[TD="align: center"]29[/TD]

[TD="align: center"]45[/TD]
[TD="align: center"]100[/TD]
[TD="align: center"][/TD]
[TD="align: center"]75[/TD]

[TD="align: center"]30[/TD]

[TD="align: center"]15[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]

</tbody>

Sheet1

Excel 2013

	T	U	V	W	X

<tbody>
[TD="align: center"]3[/TD]
[TD="align: right"][/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]3[/TD]
[TD="align: center"]4[/TD]

[TD="align: center"]4[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]3[/TD]
[TD="align: center"]5[/TD]
[TD="align: center"]6[/TD]

[TD="align: center"]5[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]4[/TD]
[TD="align: center"]7[/TD]
[TD="align: center"]10[/TD]

[TD="align: center"]~[/TD]
[TD="align: center"]~[/TD]
[TD="align: center"]~[/TD]
[TD="align: center"]~[/TD]
[TD="align: center"]~[/TD]
[TD="align: center"]~[/TD]

[TD="align: center"]47[/TD]
[TD="align: center"]44[/TD]
[TD="align: center"]14[/TD]
[TD="align: center"]29[/TD]
[TD="align: center"]48[/TD]
[TD="align: center"]67[/TD]

[TD="align: center"]48[/TD]
[TD="align: center"]45[/TD]
[TD="align: center"]15[/TD]
[TD="align: center"]30[/TD]
[TD="align: center"]50[/TD]
[TD="align: center"]70[/TD]

</tbody>

Sheet2

I can't seem to work out the correct sequencing of IFERROR & INDEX & IF & MAX. I keep getting #N/A, #REF , etc.. This, however, works if one or more Ratings are 45 or less. It's the 100 that blows it up. Any ideas? Am I WAY off base here?

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<tbody>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
[TR]
[TH="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]J30[/TH]
[TD="align: left"]=IFERROR(IF(MAX(IFERROR(INDEX(Sheet2!$T$3:$X$48,MATCH($J$29,Sheet2!$T$3:$T$48,0),MATCH(1,Sheet2!$T$3:$X$3,0)),0),IFERROR(INDEX(Sheet2!$T$3:$X$48,MATCH($K$29,Sheet2!$T$3:$T$48,0),MATCH(2,Sheet2!$T$3:$X$3,0)),0),IFERROR(INDEX(Sheet2!$T$3:$X$48,MATCH($L$29,Sheet2!$T$3:$T$48,0),MATCH(3,Sheet2!$T$3:$X$3,0)),0),IFERROR(INDEX(Sheet2!$T$3:$X$48,MATCH($M$29,Sheet2!$T$3:$T$48,0),MATCH(4,Sheet2!$T$3:$X$3,0)),0))=0,"",(MAX(IFERROR(INDEX(Sheet2!$T$3:$X$48,MATCH($J$29,Sheet2!$T$3:$T$48,0),MATCH(1,Sheet2!$T$3:$X$3,0)),0),IFERROR(INDEX(Sheet2!$T$3:$X$48,MATCH($K$29,Sheet2!$T$3:$T$48,0),MATCH(2,Sheet2!$T$3:$X$3,0)),0),IFERROR(INDEX(Sheet2!$T$3:$X$48,MATCH($L$29,Sheet2!$T$3:$T$48,0),MATCH(3,Sheet2!$T$3:$X$3,0)),0),IFERROR(INDEX(Sheet2!$T$3:$X$48,MATCH($M$29,Sheet2!$T$3:$T$48,0),MATCH(4,Sheet2!$T$3:$X$3,0)),0)))),"")[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

 

[AhoyNC]

Is this what you are trying to do?
Copy formula across.

Excel Workbook
	I	J	K	L	M
28	LEVEL	1	2	3	4
29	AVERAGE RATING	45	100		75
30	DEDUCT VALUE	15	30		70
Sheet1

Excel Workbook
	T	U	V	W	X
3		1	2	3	4
4	1	1	3	5	6
5	2	2	4	7	10
6	3	10	12	13	14
47	44	14	29	48	67
48	45	15	30	50	70
Sheet2

 

[tmmcentyre]

Almost! I forgot to mention that I only need the highest of the four Deduct Values from the INDEX table, 70 in this example. I'm trying to use an array MAX(IF but #N/A result from L29 kills it.

{=IFERROR(INDEX(Sheet2!$U$4:$X$48,MAX(
IF(MATCH($J$29,Sheet2!$T$4:$T$48,1),MATCH($J$28,Sheet2!$U$3:$X$3,0)),
IF(MATCH($K$29,Sheet2!$T$4:$T$48,1),MATCH($K$28,Sheet2!$U$3:$X$3,0)),
IF(MATCH($L$29,Sheet2!$T$4:$T$48,1),MATCH($L$28,Sheet2!$U$3:$X$3,0)),
IF(MATCH($M$29,Sheet2!$T$4:$T$48,1),MATCH($M$28,Sheet2!$U$3:$X$3,0)))),"")}

 

[KolGuyXcel]

Almost! I forgot to mention that I only need the highest of the four Deduct Values from the INDEX table, 70 in this example. I'm trying to use an array MAX(IF but #N/A result from L29 kills it.

Even putting some fictitious values like 50, 60 etc. in cell L29, the formula is yielding error value!

Try this!

Select the 4 cells J30:M30 in Sheet1.
Then enter the following formula as array formula in the cells.
=MAX(IFERROR(INDEX(Sheet2!$U$4:$X$48,MATCH(J$29:M$29,Sheet2!$T$4:$T$48,1),MATCH(J$28:M$28,Sheet2!$U$3:$X$3,0)),0)).

All the 4 cells should display 70.

By the way, I may have misunderstood the problem. But it seems that the values in the range U4:X48 in Sheet2 are already sorted in ascending order top to bottom and left to right. So X48 would always be the highest value in this range!

 

[AhoyNC]

Maybe:

Excel Workbook
	I	J	K	L	M
28	LEVEL	1	2	3	4
29	AVERAGE RATING	45	100		75
30	DEDUCT VALUE	70
Sheet1

Excel Workbook
	T	U	V	W	X
3		1	2	3	4
4	1	1	3	5	6
5	2	2	4	7	10
6	3	10	12	13	14
47	44	14	129	48	67
48	45	15	30	50	70
Sheet2

 

[tmmcentyre]

Is this what you are trying to do?
Copy formula across.

Sheet1

	I	J	K	L	M
AVERAGE RATING
DEDUCT VALUE

<colgroup><col style="width:30px; "><col style="width:132px;"><col style="width:64px;"><col style="width:64px;"><col style="width:64px;"><col style="width:64px;"></colgroup><tbody>
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]28[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=c0c0c0]#c0c0c0[/URL] "]LEVEL[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=c0c0c0]#c0c0c0[/URL] , align: center"]1[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=c0c0c0]#c0c0c0[/URL] , align: center"]2[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=c0c0c0]#c0c0c0[/URL] , align: center"]3[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=c0c0c0]#c0c0c0[/URL] , align: center"]4[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]29[/TD]

[TD="align: right"]45[/TD]
[TD="align: right"]100[/TD]

[TD="align: right"]75[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]30[/TD]

[TD="align: right"]15[/TD]
[TD="align: right"]30[/TD]

[TD="align: right"]70[/TD]

</tbody>

Spreadsheet Formulas

Cell Formula J30 =IFERROR(INDEX(Sheet2!$U$4:$X$48,MATCH(J$29,Sheet2!$T$4:$T$48,1),MATCH(Sheet1!J$28,Sheet2!$U$3:$X$3,0)),"") <tbody> </tbody>

<tbody>
</tbody>

After rethinking the spreadsheet, I'm going with this. I'll get the MAX of the four possible Deduct Values in the Final Score formula. Thanks!