Index match duplicate values, need to ignore zeros or blanks in range

[LordByron]

Hi everyone,

I'm using the below formula to return 2 pieces of data. The data is a range of numbers from 0 to 4 inclusive. I am using this formula to return the title of the largest and second largest value without duplication.

=INDEX($b2:$g2,MATCH(LARGE($b2:$g2-COLUMN($b2:$g2)/10\^10,COLUMNS($b6:b6)),$b2:$g2-COLUMN($b2:$g2)/10\^10,0))

However - I have a problem. I need the formula to incorporate an additional function - I need the formula to fetch values greater than 0 only. i.e. 1-4 inclusive

How could I incorporate either greater than 0 into the formula - or could we incorporate ignore blank cells into the formula and I can adjust the data to only return values greater than 0?

Attachment enclosed.

Thanks!

[RaulSerg]

Your formula is not even valid: /10\^10 is not a valid expression that is recognised, at least in my Excel.

See attached if that is what you have in mind.

Also, what happens if all values are 0 or all values are 4?

Regards

[LordByron]

Your formula is not even valid: /10\^10 is not a valid expression that is recognised, at least in my Excel.

See attached if that is what you have in mind.

Also, what happens if all values are 0 or all values are 4?

Regards

Hi Raul,

Thanks for the reply. It seems to work fine with me, as an array formula. I have enclosed an attachement to my original post for clarification.

[oeldere]

I change the date (with VBA code) from row into column (see sheet Oeldere).

After that a pivot table.

[Bo_Ry]

Please try at J7

=IFERROR(INDEX($A$4:$H$4,MOD(SMALL(IF(B7:H7,COLUMN(B7:H7)-B7:H7*10^9),COLUMNS($J7:J7)),10^9)),"")

Confirm by Ctrl+Shift+Enter

[LordByron]

Please try at J7

=IFERROR(INDEX($A$4:$H$4,MOD(SMALL(IF(B7:H7,COLUMN(B7:H7)-B7:H7*10^9),COLUMNS($J7:J7)),10^9)),"")

Confirm by Ctrl+Shift+Enter

Definatly getting there! Thank you, so far! In the real sheet, I have the scores in cells HM800 to ID800, with the titles in HM4 to ID4 and the top 2 team results in IE800 and IF800 - when i plug the formula in on the real sheet, its blank - what changes am I required to make?

Thank you so far!

[Bo_Ry]

Please try at
IE800
=IFERROR(INDEX($HM$4:$ID$4,MOD(SMALL(IF($HM800:$ID800,COLUMN($HM$4:$ID$4)-$HM800:$ID800*10^9),COLUMNS($IE$4:IE$4)),10^9)-COLUMN($HM$4)+1),"")

or

=IFERROR(INDEX($A$4:$ID$4,MOD(SMALL(IF($HM800:$ID800,COLUMN($HM$4:$ID$4)-$HM800:$ID800*10^9),COLUMNS($IE$4:IE$4)),10^9)),"")

[LordByron]

Please try at
IE800
=IFERROR(INDEX($HM$4:$ID$4,MOD(SMALL(IF($HM800:$ID800,COLUMN($HM$4:$ID$4)-$HM800:$ID800*10^9),COLUMNS($IE$4:IE$4)),10^9)-COLUMN($HM$4)+1),"")

or

=IFERROR(INDEX($A$4:$ID$4,MOD(SMALL(IF($HM800:$ID800,COLUMN($HM$4:$ID$4)-$HM800:$ID800*10^9),COLUMNS($IE$4:IE$4)),10^9)),"")

Friend, you are a legend! Thank you so much! It is much appreciated!