Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
VLOOKUP/Index&Match data format issue | Excel Worksheet Functions | |||
index match array function-returning only first match, need last. | Excel Worksheet Functions | |||
How do I display more than one match in a Index/Match formula? | Excel Worksheet Functions | |||
index,match,match on un-sorted data | Excel Worksheet Functions | |||
Help with complex index array issue | Excel Worksheet Functions |