Array formula combined with Lookup

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.....................50

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 Format>Conditional 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@excelforum-nospam.com> wrote in
message news:Kevin.Gallagher.24124n_1141278001.5412@excelforum-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)*(list!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
>