index match issue

[StephenAccountant]

ok here is my formula:

=INDEX('Performance Summary'!D4:H4,MATCH(MIN('Performance
Summary'!D24:H24),'Performance Summary'!D24:H24,0))

Basically the data in cells D4:H4 are years like 2008, 2007 etc.

They are the values I need returned.

However - in my formula with the MIN section - I only want to include those
that are greater then 0.

So I need to have cells D24:H24 in there but if say H24 is zero then I don't
want it to include that.

 

[Max]

Replace with this, array-enter by pressing CTRL+SHIFT+ENTER:
=INDEX('Performance Summary'!D4:H4,MATCH(MIN(IF('Performance
Summary'!D24:H24>0,'Performance Summary'!D24:H24)),IF('Performance
Summary'!D24:H24>0,'Performance Summary'!D24:H24),0))

 

[T. Valko]

Try this:

Assuming all values are positive.

=INDEX(D4:H4,MATCH(SMALL(D24:H24,COUNTIF(D24:H24,0)+1),D24:H24,0))

If there are multiple instances of the min the formula will "find" the first
instance from left to right.

If all the values are 0s or there are no values then the formula will return
an error. You can trap that error and return a blank:

=IF(SUM(D24:H24),INDEX(D4:H4,MATCH(SMALL(D24:H24,COUNTIF(D24:H24,0)+1),D24:H24,0)),"")