Array formula combined with Lookup

  • Thread starter Thread starter Kevin Gallagher
  • Start date Start date
K

Kevin Gallagher

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
 
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"
 
Thanks for the help Biff.

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

e.g. the value in column Z

Cheers
Kevi
 
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"
 
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"
 
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)
 
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
 
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
 
Biff,

This still does not work where there is another instance of the valu
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
Kevi
 
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"
 
Back
Top