index match issue
 

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.

index match issue
 

Replace with this, array-enter by pressing CTRL+SHIFT+ENTER:
=INDEX('Performance Summary'!D4:H4,MATCH(MIN(IF('Performance
Summary'!D24:H240,'Performance Summary'!D24:H24)),IF('Performance
Summary'!D24:H240,'Performance Summary'!D24:H24),0))
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"StephenAccountant" wrote:
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.

index match issue
 

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,C OUNTIF(D24:H24,0)+1),D24:H24,0)),"")

--
Biff
Microsoft Excel MVP


"StephenAccountant" wrote in
message ...
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.