If there is more than one instance of:
list!C3:C2000=B4
*AND*
MIN(IF(list!C3:C2000=B4,list!I3:I2000))
The formula will return the corresponding value of the FIRST instance.
For example:
B4 = Y
column C..........column I..........column Z
N.........................10...................100
Y.........................10...................125
N.........................20...................110
Y.........................30...................105
Y.........................10.....................5 0
There are 2 instances where column C = Y and column I = MIN if column C = Y
(10).
The default functionality of Excels calculation process ALWAYS "finds" the
first instance of anything. If you want to return ALL instances or a
specific instance: (array entered)
=INDEX(List!Z$3:Z$20,SMALL(IF((List!C$3:C$20=B$4)* (List!I$3:I$20=MIN(IF(List!C$3:C$20=B$4,List!I$3:I $20))),ROW(List!C$3:C$20)-ROW(List!C$3)+1),ROWS($1:1)))
Copy down until you get #NUM! errors meaning the data has been exhausted.
If you want a specific instance, change this portion:
ROWS($1:1)
To:
ROW(n:n)
Where n = instance number
If you want an error trap so that you don't get #NUM! the formula will be
twice as long! I would suggest just using conditional formatting to hide
them.
Select the cells that hold these formulas
Goto FormatConditional Formatting
Formula is: =ISERROR(cell_reference)
Click the Format button
Set the font color to be the same as the background color.
OK out.
If you still can't get things working properly after this, I would need to
see the file to figure out what's going on.
Biff
"Kevin Gallagher"
<Kevin.Gallagher.24124n_1141278001.5412@excelfor um-nospam.com wrote in
message news:Kevin.Gallagher.24124n_1141278001.5412@excelf orum-nospam.com...
Biff,
This still does not work where there is another instance of the value
within subset of =B4
=INDEX(list!U3:U2000,MATCH(1,(list!C3:C2000=B4)*(l ist!I3:I2000=MIN(IF(list!C3:C2000=B4,list!I3:I2000 ))),0))
Cheers
Kevin
--
Kevin Gallagher
------------------------------------------------------------------------
Kevin Gallagher's Profile:
http://www.excelforum.com/member.php...fo&userid=7459
View this thread: http://www.excelforum.com/showthread...hreadid=518104