|
Array formula combined with Lookup
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 |
Array formula combined with Lookup
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" <Kevin.Gallagher.240wt0_1141271101.9736@excelfor um-nospam.com wrote in message news:Kevin.Gallagher.240wt0_1141271101.9736@excelf orum-nospam.com... 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 |
Array formula combined with Lookup
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 |
Array formula combined with Lookup
Assume 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 & 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" <Kevin.Gallagher.240wt0_1141271101.9736@excelfor um-nospam.com wrote in message news:Kevin.Gallagher.240wt0_1141271101.9736@excelf orum-nospam.com... 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 |
Array formula combined with Lookup
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" <Kevin.Gallagher.240zcm_1141274401.6915@excelfor um-nospam.com wrote in message news:Kevin.Gallagher.240zcm_1141274401.6915@excelf orum-nospam.com... 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 |
Array formula combined with Lookup
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: http://www.excelforum.com/member.php...fo&userid=7459 View this thread: http://www.excelforum.com/showthread...hreadid=518104 |
Array formula combined with Lookup
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" wrote in message ... 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" <Kevin.Gallagher.240zcm_1141274401.6915@excelfor um-nospam.com wrote in message news:Kevin.Gallagher.240zcm_1141274401.6915@excelf orum-nospam.com... 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 |
Array formula combined with Lookup
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" wrote in message ... Assume 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 & 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" <Kevin.Gallagher.240wt0_1141271101.9736@excelfor um-nospam.com wrote in message news:Kevin.Gallagher.240wt0_1141271101.9736@excelf orum-nospam.com... 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 |
Array formula combined with Lookup
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 |
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.....................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 |
| All times are GMT +1. The time now is 04:15 AM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com