Home |
Search |
Today's Posts |
#1
|
|||
|
|||
lookup - return minimum value
I am looking for a formula to return the minimum value from a table (below)
Red Green Blue Yellow Orange PET 6 8 14 3 2 OAK 5 6 7 8 12 SJC 2 7 11 8 5 If the following lookup info is entered into 4 cells(below) PET Red Blue Yellow - The value "3" is returned since it is the minimum value in the "PET row" of the 3 items listed Thanks for any help Steve |
#2
|
|||
|
|||
One try ..
Assuming this table is in A1:F4 Red Green Blue Yellow Orange PET 6 8 14 3 2 OAK 5 6 7 8 12 SJC 2 7 11 8 5 and this "criteria range" is in A6:D6 PET Red Blue Yellow Put in say, E6: =MIN(TRANSPOSE(OFFSET($A$1,MATCH(A6,A:A,0)-1,MATCH(B6:D6,B1:F1,0)))) Array-enter the formula, i.e. press CTRL+SHIFT+ENTER instead of just pressing ENTER -- Rgds Max xl 97 --- GMT+8, 1° 22' N 103° 45' E xdemechanik <atyahoo<dotcom ---- "Steve R" wrote in message ... I am looking for a formula to return the minimum value from a table (below) Red Green Blue Yellow Orange PET 6 8 14 3 2 OAK 5 6 7 8 12 SJC 2 7 11 8 5 If the following lookup info is entered into 4 cells(below) PET Red Blue Yellow - The value "3" is returned since it is the minimum value in the "PET row" of the 3 items listed Thanks for any help Steve |
#3
|
|||
|
|||
Another try with the same layout:
=MIN(VLOOKUP(A6,A2:F4,MATCH(B6:D6,A1:F1,0),0)) array entered Alan Beban Max wrote: One try .. Assuming this table is in A1:F4 Red Green Blue Yellow Orange PET 6 8 14 3 2 OAK 5 6 7 8 12 SJC 2 7 11 8 5 and this "criteria range" is in A6:D6 PET Red Blue Yellow Put in say, E6: =MIN(TRANSPOSE(OFFSET($A$1,MATCH(A6,A:A,0)-1,MATCH(B6:D6,B1:F1,0)))) Array-enter the formula, i.e. press CTRL+SHIFT+ENTER instead of just pressing ENTER -- Rgds Max xl 97 --- GMT+8, 1° 22' N 103° 45' E xdemechanik <atyahoo<dotcom ---- "Steve R" wrote in message ... I am looking for a formula to return the minimum value from a table (below) Red Green Blue Yellow Orange PET 6 8 14 3 2 OAK 5 6 7 8 12 SJC 2 7 11 8 5 If the following lookup info is entered into 4 cells(below) PET Red Blue Yellow - The value "3" is returned since it is the minimum value in the "PET row" of the 3 items listed Thanks for any help Steve |
#4
|
|||
|
|||
Thanks to both of you. Both or your solutions worked. Thank You
I now have a followup question Is there a way for the fomula to work if the criteria row only has one, two values (in b6:d6) ? (of course there needs to be criteria in A6) With the formula Max gave, if 3 criteria are not entered I get #N/A. Steve -- Steve "Alan Beban" wrote in message ... Another try with the same layout: =MIN(VLOOKUP(A6,A2:F4,MATCH(B6:D6,A1:F1,0),0)) array entered Alan Beban Max wrote: One try .. Assuming this table is in A1:F4 Red Green Blue Yellow Orange PET 6 8 14 3 2 OAK 5 6 7 8 12 SJC 2 7 11 8 5 and this "criteria range" is in A6:D6 PET Red Blue Yellow Put in say, E6: =MIN(TRANSPOSE(OFFSET($A$1,MATCH(A6,A:A,0)-1,MATCH(B6:D6,B1:F1,0)))) Array-enter the formula, i.e. press CTRL+SHIFT+ENTER instead of just pressing ENTER -- Rgds Max xl 97 --- GMT+8, 1° 22' N 103° 45' E xdemechanik <atyahoo<dotcom ---- "Steve R" wrote in message ... I am looking for a formula to return the minimum value from a table (below) Red Green Blue Yellow Orange PET 6 8 14 3 2 OAK 5 6 7 8 12 SJC 2 7 11 8 5 If the following lookup info is entered into 4 cells(below) PET Red Blue Yellow - The value "3" is returned since it is the minimum value in the "PET row" of the 3 items listed Thanks for any help Steve |
#5
|
|||
|
|||
Try the following array formula...
=MIN(VLOOKUP(A6,A2:F4,SMALL(IF(B1:F1=TRANSPOSE(B6: D6),COLUMN(B2:F2)),ROW( INDIRECT("1:"&COUNTA(B6:D6)))),0)) ....confirmed with CONTROL+SHIFT+ENTER. Hope this helps! In article , "Steve Rolls" wrote: Thanks to both of you. Both or your solutions worked. Thank You I now have a followup question Is there a way for the fomula to work if the criteria row only has one, two values (in b6:d6) ? (of course there needs to be criteria in A6) With the formula Max gave, if 3 criteria are not entered I get #N/A. Steve -- Steve "Alan Beban" wrote in message ... Another try with the same layout: =MIN(VLOOKUP(A6,A2:F4,MATCH(B6:D6,A1:F1,0),0)) array entered Alan Beban Max wrote: One try .. Assuming this table is in A1:F4 Red Green Blue Yellow Orange PET 6 8 14 3 2 OAK 5 6 7 8 12 SJC 2 7 11 8 5 and this "criteria range" is in A6:D6 PET Red Blue Yellow Put in say, E6: =MIN(TRANSPOSE(OFFSET($A$1,MATCH(A6,A:A,0)-1,MATCH(B6:D6,B1:F1,0)))) Array-enter the formula, i.e. press CTRL+SHIFT+ENTER instead of just pressing ENTER -- Rgds Max xl 97 --- GMT+8, 1° 22' N 103° 45' E xdemechanik <atyahoo<dotcom ---- "Steve R" wrote in message ... I am looking for a formula to return the minimum value from a table (below) Red Green Blue Yellow Orange PET 6 8 14 3 2 OAK 5 6 7 8 12 SJC 2 7 11 8 5 If the following lookup info is entered into 4 cells(below) PET Red Blue Yellow - The value "3" is returned since it is the minimum value in the "PET row" of the 3 items listed Thanks for any help Steve |
#6
|
|||
|
|||
Correction...
=MIN(VLOOKUP(A6,A2:F4,SMALL(IF(B1:F1=TRANSPOSE(B6: D6),COLUMN(B1:F1)),ROW( INDIRECT("1:"&COUNTA(B6:D6)))),0)) ....confirmed with CONTROL+SHIFT+ENTER. Note that the change is not really significant and my previous formula works just the same. Hope this helps! In article , Domenic wrote: Try the following array formula... =MIN(VLOOKUP(A6,A2:F4,SMALL(IF(B1:F1=TRANSPOSE(B6: D6),COLUMN(B2:F2)),ROW( INDIRECT("1:"&COUNTA(B6:D6)))),0)) ...confirmed with CONTROL+SHIFT+ENTER. Hope this helps! |
#7
|
|||
|
|||
Excellent ! Thank You all.
-- Steve "Domenic" wrote in message ... Try the following array formula... =MIN(VLOOKUP(A6,A2:F4,SMALL(IF(B1:F1=TRANSPOSE(B6: D6),COLUMN(B2:F2)),ROW( INDIRECT("1:"&COUNTA(B6:D6)))),0)) ...confirmed with CONTROL+SHIFT+ENTER. Hope this helps! In article , "Steve Rolls" wrote: Thanks to both of you. Both or your solutions worked. Thank You I now have a followup question Is there a way for the fomula to work if the criteria row only has one, two values (in b6:d6) ? (of course there needs to be criteria in A6) With the formula Max gave, if 3 criteria are not entered I get #N/A. Steve -- Steve "Alan Beban" wrote in message ... Another try with the same layout: =MIN(VLOOKUP(A6,A2:F4,MATCH(B6:D6,A1:F1,0),0)) array entered Alan Beban Max wrote: One try .. Assuming this table is in A1:F4 Red Green Blue Yellow Orange PET 6 8 14 3 2 OAK 5 6 7 8 12 SJC 2 7 11 8 5 and this "criteria range" is in A6:D6 PET Red Blue Yellow Put in say, E6: =MIN(TRANSPOSE(OFFSET($A$1,MATCH(A6,A:A,0)-1,MATCH(B6:D6,B1:F1,0)))) Array-enter the formula, i.e. press CTRL+SHIFT+ENTER instead of just pressing ENTER -- Rgds Max xl 97 --- GMT+8, 1° 22' N 103° 45' E xdemechanik <atyahoo<dotcom ---- "Steve R" wrote in message ... I am looking for a formula to return the minimum value from a table (below) Red Green Blue Yellow Orange PET 6 8 14 3 2 OAK 5 6 7 8 12 SJC 2 7 11 8 5 If the following lookup info is entered into 4 cells(below) PET Red Blue Yellow - The value "3" is returned since it is the minimum value in the "PET row" of the 3 items listed Thanks for any help Steve |
#8
|
|||
|
|||
"Steve Rolls" wrote
Excellent ! Thank You all. You're welcome, Steve ! Liked the neater solution posted by Alan B, and Domenic's "flexible" solution when you threw in the additional condition (urgh! <g) -- Rgds Max xl 97 --- GMT+8, 1° 22' N 103° 45' E xdemechanik <atyahoo<dotcom ---- |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I lookup and return different values when the lookup value. | Excel Discussion (Misc queries) | |||
Lookup values in a list and return multiple rows of data | Excel Worksheet Functions | |||
Excel Lookup Functions | Excel Worksheet Functions | |||
Another Lookup function, please | Excel Worksheet Functions | |||
double lookup, nest, or macro? | Excel Worksheet Functions |