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
|