software

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: www.excelforum.com/member.php...foamp;userid=7459
View this thread: www.excelforum.com/showthread...hreadid=518104Hi!

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

quot;Kevin Gallagherquot;
lt;Kevin.Gallagher.240wt0_1141271101.9736@excelfor um-nospam.comgt; wrote in
message news:Kevin.Gallagher.240wt0_1141271101.9736@excelf orum-nospam.com...
gt;
gt; I have this which works....
gt;
gt; {=MIN(IF((list!$C$3:$C$7202=B4),list!I3:I7202))}
gt;
gt; This formala returns the value in column I (the minimum value that
gt; meets the criteria), however once I find the minimum, I would also like
gt; to know the values in the other columns within the same row (e.g. other
gt; information about the row entry).
gt;
gt; How can I find other infomation in the same row as the value I am
gt; finding with the array formala above.
gt;
gt; All help woudl be very much appreciated
gt;
gt;
gt; --
gt; Kevin Gallagher
gt; ------------------------------------------------------------------------
gt; Kevin Gallagher's Profile:
gt; www.excelforum.com/member.php...foamp;userid=7459
gt; View this thread: www.excelforum.com/showthread...hreadid=518104
gt;

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: www.excelforum.com/member.php...foamp;userid=7459
View this thread: www.excelforum.com/showthread...hreadid=518104Assume you want the value in column J

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

entered with ctrl shift amp; 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

quot;Kevin Gallagherquot;
lt;Kevin.Gallagher.240wt0_1141271101.9736@excelfor um-nospam.comgt; wrote in
message news:Kevin.Gallagher.240wt0_1141271101.9736@excelf orum-nospam.com...
gt;
gt; I have this which works....
gt;
gt; {=MIN(IF((list!$C$3:$C$7202=B4),list!I3:I7202))}
gt;
gt; This formala returns the value in column I (the minimum value that
gt; meets the criteria), however once I find the minimum, I would also like
gt; to know the values in the other columns within the same row (e.g. other
gt; information about the row entry).
gt;
gt; How can I find other infomation in the same row as the value I am
gt; finding with the array formala above.
gt;
gt; All help woudl be very much appreciated
gt;
gt;
gt; --
gt; Kevin Gallagher
gt; ------------------------------------------------------------------------
gt; Kevin Gallagher's Profile:
gt; www.excelforum.com/member.php...foamp;userid=7459
gt; View this thread: www.excelforum.com/showthread...hreadid=518104
gt;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

quot;Kevin Gallagherquot;
lt;Kevin.Gallagher.240zcm_1141274401.6915@excelfor um-nospam.comgt; wrote in
message news:Kevin.Gallagher.240zcm_1141274401.6915@excelf orum-nospam.com...
gt;
gt; Thanks for the help Biff.
gt;
gt; I just need to return the contents of ONE of the cells (in a specified
gt; column) from the same row as the minimum value returned.
gt;
gt; e.g. the value in column Z
gt;
gt; Cheers
gt; Kevin
gt;
gt;
gt; --
gt; Kevin Gallagher
gt; ------------------------------------------------------------------------
gt; Kevin Gallagher's Profile:
gt; www.excelforum.com/member.php...foamp;userid=7459
gt; View this thread: www.excelforum.com/showthread...hreadid=518104
gt;

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))--
Kevin Gallagher
------------------------------------------------------------------------
Kevin Gallagher's Profile: www.excelforum.com/member.php...foamp;userid=7459
View this thread: www.excelforum.com/showthread...hreadid=518104Ooops!

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

quot;Biffquot; gt; wrote in message
...
gt; Try this:
gt;
gt; Entered as an array using the key combo of CTRL,SHIFT,ENTER:
gt;
gt; =INDEX(List!Z3:Z7202,MATCH(TRUE,List!I3:I7202=MIN( IF(List!C3:C7202=B4,List!I3:I7202)),0))
gt;
gt; Biff
gt;
gt; quot;Kevin Gallagherquot;
gt; lt;Kevin.Gallagher.240zcm_1141274401.6915@excelfor um-nospam.comgt; wrote in
gt; message
gt; news:Kevin.Gallagher.240zcm_1141274401.6915@excelf orum-nospam.com...
gt;gt;
gt;gt; Thanks for the help Biff.
gt;gt;
gt;gt; I just need to return the contents of ONE of the cells (in a specified
gt;gt; column) from the same row as the minimum value returned.
gt;gt;
gt;gt; e.g. the value in column Z
gt;gt;
gt;gt; Cheers
gt;gt; Kevin
gt;gt;
gt;gt;
gt;gt; --
gt;gt; Kevin Gallagher
gt;gt; ------------------------------------------------------------------------
gt;gt; Kevin Gallagher's Profile:
gt;gt; www.excelforum.com/member.php...foamp;userid=7459
gt;gt; View this thread:
gt;gt; www.excelforum.com/showthread...hreadid=518104
gt;gt;
gt;
gt;
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

quot;Peo Sjoblomquot; gt; wrote in message
...
gt; Assume you want the value in column J
gt;
gt; =INDEX(list!J3:J7202,MATCH(MIN(IF((list!$C$3:$C$72 02=$B$4),list!$I$3:$I$7202)),list!$I$3:$I$7202,0))
gt;
gt; entered with ctrl shift amp; enter, then copied across it will return K, L
gt; etc
gt;
gt;
gt;
gt; --
gt;
gt; Regards,
gt;
gt; Peo Sjoblom
gt;
gt; Northwest Excel Solutions
gt;
gt; www.nwexcelsolutions.com
gt;
gt; (remove ^^ from email address)
gt;
gt; Portland, Oregon
gt;
gt;
gt;
gt;
gt; quot;Kevin Gallagherquot;
gt; lt;Kevin.Gallagher.240wt0_1141271101.9736@excelfor um-nospam.comgt; wrote in
gt; message
gt; news:Kevin.Gallagher.240wt0_1141271101.9736@excelf orum-nospam.com...
gt;gt;
gt;gt; I have this which works....
gt;gt;
gt;gt; {=MIN(IF((list!$C$3:$C$7202=B4),list!I3:I7202))}
gt;gt;
gt;gt; This formala returns the value in column I (the minimum value that
gt;gt; meets the criteria), however once I find the minimum, I would also like
gt;gt; to know the values in the other columns within the same row (e.g. other
gt;gt; information about the row entry).
gt;gt;
gt;gt; How can I find other infomation in the same row as the value I am
gt;gt; finding with the array formala above.
gt;gt;
gt;gt; All help woudl be very much appreciated
gt;gt;
gt;gt;
gt;gt; --
gt;gt; Kevin Gallagher
gt;gt; ------------------------------------------------------------------------
gt;gt; Kevin Gallagher's Profile:
gt;gt; www.excelforum.com/member.php...foamp;userid=7459
gt;gt; View this thread:
gt;gt; www.excelforum.com/showthread...hreadid=518104
gt;gt;
gt;

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: www.excelforum.com/member.php...foamp;userid=7459
View this thread: www.excelforum.com/showthread...hreadid=518104If 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 quot;findsquot; 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 Formatgt;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

quot;Kevin Gallagherquot;
lt;Kevin.Gallagher.24124n_1141278001.5412@excelfor um-nospam.comgt; wrote in
message news:Kevin.Gallagher.24124n_1141278001.5412@excelf orum-nospam.com...
gt;
gt; Biff,
gt;
gt; This still does not work where there is another instance of the value
gt; within subset of =B4
gt;
gt; =INDEX(list!U3:U2000,MATCH(1,(list!C3:C2000=B4)*(l ist!I3:I2000=MIN(IF(list!C3:C2000=B4,list!I3:I2000 ))),0))
gt;
gt;
gt; Cheers
gt; Kevin
gt;
gt;
gt; --
gt; Kevin Gallagher
gt; ------------------------------------------------------------------------
gt; Kevin Gallagher's Profile:
gt; www.excelforum.com/member.php...foamp;userid=7459
gt; View this thread: www.excelforum.com/showthread...hreadid=518104
gt;