INDEX and MATCH for MAX value of non-adjacent range in seperate sheet

[MMndolo]

Hi

I'm having some problems and was hoping I might get some help here having come up with nothing from searching the forum

I want to return the text string in a the Cell to the left of the highest value cell in a non-adjacent range from another worksheet to the one where I'm entering the formula

I've tried using the unweildy forumla below

<colgroup><col width="247"></colgroup><tbody>
[TD="width: 247"][/TD]

</tbody>

=INDEX((Quality Checks'!B21,'Quality Checks'!B24,'Quality Checks'!B27,'Quality Checks'!B30,'Quality Checks'!B33),MATCH(MAX((Quality Checks'!C21,'Quality Checks'!C24,'Quality Checks'!C27,'Quality Checks'!C30,'Quality Checks'!C33)),(Quality Checks'!C21,'Quality Checks'!C24,'Quality Checks'!C27,'Quality Checks'!C30,'Quality Checks'!C33),0))

and

=INDEX(Quality Checks'!B3,'Quality Checks'!B6,'Quality Checks'!B9,'Quality Checks'!B12,'Quality Checks'!B15,MATCH(MAX(Quality Checks'!C3,'Quality Checks'!C6,'Quality Checks'!C9,'Quality Checks'!C12,'Quality Checks'!C15),Quality Checks'!C3,'Quality Checks'!C6,'Quality Checks'!C9,'Quality Checks'!C12,'Quality Checks'!C15,0))

both of which produce this error

For context this is the sheet that I'm referenceing

As it contain's hidden rows the range is non-adjacent, in the summary sheet I'm trying to build I've got one collumn showing the highest and lowest Score for each month and I want the next colllumn to return the actual Incident Ref.

Any help much appreciated

 

[MMndolo]

First post and my images fail

Hi

I'm having some problems and was hoping I might get some help here having come up with nothing from searching the forum

I want to return the text string in a the Cell to the left of the highest value cell in a non-adjacent range from another worksheet to the one where I'm entering the formula

I've tried using the unweildy forumla below

<tbody>
</tbody>

=INDEX((Quality Checks'!B21,'Quality Checks'!B24,'Quality Checks'!B27,'Quality Checks'!B30,'Quality Checks'!B33),MATCH(MAX((Quality Checks'!C21,'Quality Checks'!C24,'Quality Checks'!C27,'Quality Checks'!C30,'Quality Checks'!C33)),(Quality Checks'!C21,'Quality Checks'!C24,'Quality Checks'!C27,'Quality Checks'!C30,'Quality Checks'!C33),0))

and

=INDEX(Quality Checks'!B3,'Quality Checks'!B6,'Quality Checks'!B9,'Quality Checks'!B12,'Quality Checks'!B15,MATCH(MAX(Quality Checks'!C3,'Quality Checks'!C6,'Quality Checks'!C9,'Quality Checks'!C12,'Quality Checks'!C15),Quality Checks'!C3,'Quality Checks'!C6,'Quality Checks'!C9,'Quality Checks'!C12,'Quality Checks'!C15,0))

both of which produce this error

For context this is the sheet that I'm referenceing

As it contain's hidden rows the range is non-adjacent, in the summary sheet I'm trying to build I've got one collumn showing the highest and lowest Score for each month and I want the next colllumn to return the actual Incident Ref.

Any help much appreciated

 

[Aladin Akyurek]

In A1 of Sheet1 control+shift+enter, not just enter, and copy down:

=IFERROR(INDEX('Quality Checks'!$B$21:$B$33,SMALL(IF(IF(MOD(ROW('Quality Checks'!$C$21:$C$33)-ROW('Quality Checks'!$C$21),3)=0,
    'Quality Checks'!$C$21:$C$33)=MAX('Quality Checks'!$C$21:$C$33),ROW('Quality Checks'!$C$21:$C$33)-ROW('Quality Checks'!$C$21)+1),
    ROWS($A$1:A1))),"")

​

 

[MMndolo]

I've tried this in both the Sheet I have named 'Qualty scores' where I want to build the summary and also in a new 'sheet1' to test and it does not appear to produce any results not sure what I'm doing wrong

my summary sheet at the moment look's like this with row 3 displaying the formual used above

[TABLE="class: grid, width: 900"]
<tbody>[TR]
[TD]Period
[/TD]
[TD]Average Score
[/TD]
[TD]Best Score
[/TD]
[TD]Reference
[/TD]
[TD]Worst Score
[/TD]
[TD]Reference
[/TD]
[/TR]
[TR]
[TD]January
[/TD]
[TD]31%
[/TD]
[TD]50%
[/TD]
[TD]#123
[/TD]
[TD]0%
[/TD]
[TD]#845
[/TD]
[/TR]
[TR]
[TD]Formuala
[/TD]
[TD]='Quality Checks'!C18
[/TD]
[TD]=MAX('Quality Checks'!C3,'Quality Checks'!C6,'Quality Checks'!C9,'Quality Checks'!C12,'Quality Checks'!C15)
[/TD]
[TD][/TD]
[TD]=MIN('Quality Checks'!C3,'Quality Checks'!C6,'Quality Checks'!C9,'Quality Checks'!C12,'Quality Checks'!C15)
[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[Aladin Akyurek]

I have crafted that formula using the refereces the non-working formulas you have forwarded in your original post.

The formula looks at the data located in the sheet Quality Checks. It's implemented in A1 of Sheet1.

 

[MMndolo]

I've tried entering this into A1 of Sheet1 using control+shift+enter and nothing is displayed in any of the cells.

 

[Aladin Akyurek]

I've tried entering this into A1 of Sheet1 using control+shift+enter and nothing is displayed in any of the cells.

Try to post a small sample (not an image, also not a link) along the expected outcome for that sample.