Array formula combined with Lookup

**Kevin Gallagher** · 03-01-2006, 11:44 PM

I have this which works....

{=MIN(IF((list!$C$3:$C$7202=B4),list!I3:I7202))}

This formala returns the value in column I (the minimum value that meets the criteria), however once I find the minimum, I would also like to know the values in the other columns within the same row (e.g. other information about the row entry).

How can I find other infomation in the same row as the value I am finding with the array formala above.

All help woudl be very much appreciated

Biff · 03-02-2006, 12:30 AM

Hi!

There are a couple of ways to do this. Exactly what columns of data do you
want returned? Do you want everything from column C to column I?

Biff

"Kevin Gallagher"
<[email protected]> wrote in
message news:[email protected]...
>
> I have this which works....
>
> {=MIN(IF((list!$C$3:$C$7202=B4),list!I3:I7202))}
>
> This formala returns the value in column I (the minimum value that
> meets the criteria), however once I find the minimum, I would also like
> to know the values in the other columns within the same row (e.g. other
> information about the row entry).
>
> How can I find other infomation in the same row as the value I am
> finding with the array formala above.
>
> All help woudl be very much appreciated
>
>
> --
> 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
>

**Kevin Gallagher** · 03-02-2006, 12:38 AM

Thanks for the help Biff.

I just need to return the contents of ONE of the cells (in a specified column) from the same row as the minimum value returned.

e.g. the value in column Z

Cheers
Kevin

Peo Sjoblom · 03-02-2006, 12:55 AM

Assume you want the value in column J

=INDEX(list!J3:J7202,MATCH(MIN(IF((list!$C$3:$C$7202=$B$4),list!$I$3:$I$7202)),list!$I$3:$I$7202,0))

entered with ctrl + shift & enter, then copied across it will return K, L
etc

--

Regards,

Peo Sjoblom

Northwest Excel Solutions

www.nwexcelsolutions.com

(remove ^^ from email address)

Portland, Oregon

"Kevin Gallagher"
<[email protected]> wrote in
message news:[email protected]...
>
> I have this which works....
>
> {=MIN(IF((list!$C$3:$C$7202=B4),list!I3:I7202))}
>
> This formala returns the value in column I (the minimum value that
> meets the criteria), however once I find the minimum, I would also like
> to know the values in the other columns within the same row (e.g. other
> information about the row entry).
>
> How can I find other infomation in the same row as the value I am
> finding with the array formala above.
>
> All help woudl be very much appreciated
>
>
> --
> 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
>

Biff · 03-02-2006, 12:55 AM

Try this:

Entered as an array using the key combo of CTRL,SHIFT,ENTER:

=INDEX(List!Z3:Z7202,MATCH(TRUE,List!I3:I7202=MIN(IF(List!C3:C7202=B4,List!I3:I7202)),0))

Biff

"Kevin Gallagher"
<[email protected]> wrote in
message news:[email protected]...
>
> Thanks for the help Biff.
>
> I just need to return the contents of ONE of the cells (in a specified
> column) from the same row as the minimum value returned.
>
> e.g. the value in column Z
>
> 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
>

**Kevin Gallagher** · 03-02-2006, 01:03 AM

Thanks for the help.

I tried this and it worked

=INDEX(List!Z3:Z7202,MATCH(TRUE,List!I3:I7202=MIN( IF(List!C3:C7202=B4,List!I3:I7202)),0))

Biff · 03-02-2006, 01:10 AM

Ooops!

Caught a bug!

Try this instead:

=INDEX(List!Z3:Z20,MATCH(1,(List!C3:C20=B4)*(List!I3:I20=MIN(IF(List!C3:C20=B4,List!I3:I20))),0))

Biff

"Biff" <[email protected]> wrote in message
news:[email protected]...
> Try this:
>
> Entered as an array using the key combo of CTRL,SHIFT,ENTER:
>
> =INDEX(List!Z3:Z7202,MATCH(TRUE,List!I3:I7202=MIN(IF(List!C3:C7202=B4,List!I3:I7202)),0))
>
> Biff
>
> "Kevin Gallagher"
> <[email protected]> wrote in
> message
> news:[email protected]...
>>
>> Thanks for the help Biff.
>>
>> I just need to return the contents of ONE of the cells (in a specified
>> column) from the same row as the minimum value returned.
>>
>> e.g. the value in column Z
>>
>> 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
>>
>
>

Biff · 03-02-2006, 01:15 AM

Both of our formulas could return the incorrect value IF there is another
instance of the min even if the below evaluates to FALSE:

IF((list!$C$3:$C$7202=$B$4)

This works: (tested on a smaller range)

=INDEX(List!Z3:Z20,MATCH(1,(List!C3:C20=B4)*(List!I3:I20=MIN(IF(List!C3:C20=B4,List!I3:I20))),0))

Biff

"Peo Sjoblom" <peo.sjoblom@^^nwexcelsolutions.com> wrote in message
news:[email protected]...
> Assume you want the value in column J
>
> =INDEX(list!J3:J7202,MATCH(MIN(IF((list!$C$3:$C$7202=$B$4),list!$I$3:$I$7202)),list!$I$3:$I$7202,0))
>
> entered with ctrl + shift & enter, then copied across it will return K, L
> etc
>
>
>
> --
>
> Regards,
>
> Peo Sjoblom
>
> Northwest Excel Solutions
>
> www.nwexcelsolutions.com
>
> (remove ^^ from email address)
>
> Portland, Oregon
>
>
>
>
> "Kevin Gallagher"
> <[email protected]> wrote in
> message
> news:[email protected]...
>>
>> I have this which works....
>>
>> {=MIN(IF((list!$C$3:$C$7202=B4),list!I3:I7202))}
>>
>> This formala returns the value in column I (the minimum value that
>> meets the criteria), however once I find the minimum, I would also like
>> to know the values in the other columns within the same row (e.g. other
>> information about the row entry).
>>
>> How can I find other infomation in the same row as the value I am
>> finding with the array formala above.
>>
>> All help woudl be very much appreciated
>>
>>
>> --
>> 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
>>
>

**Kevin Gallagher** · 03-02-2006, 01:38 AM

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))

However - that does not really matter - as I can just notify the user of the case e.g. (count records within subset that equal the max). This should be ok. once again - Thanks for the help.

Cheers
Kevin

Biff · 03-02-2006, 02:55 AM

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"
<[email protected]> wrote in
message news:[email protected]...
>
> 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
>