lookup - return minimum value

[Steve R]

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

 

[Max]

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 A66

PET Red Blue Yellow

Put in say, E6:

=MIN(TRANSPOSE(OFFSET($A$1,MATCH(A6,A:A,0)-1,MATCH(B66,B1:F1,0))))

Array-enter the formula,
i.e. press CTRL+SHIFT+ENTER
instead of just pressing ENTER

 

[Alan Beban]

Another try with the same layout:

=MIN(VLOOKUP(A6,A2:F4,MATCH(B66,A1:F1,0),0)) array entered

Alan Beban

 

[Steve Rolls]

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

 

[Domenic]

Try the following array formula...

=MIN(VLOOKUP(A6,A2:F4,SMALL(IF(B1:F1=TRANSPOSE(B66),COLUMN(B2:F2)),ROW(
INDIRECT("1:"&COUNTA(B66)))),0))

....confirmed with CONTROL+SHIFT+ENTER.

Hope this helps!

 

[Domenic]

Correction...

=MIN(VLOOKUP(A6,A2:F4,SMALL(IF(B1:F1=TRANSPOSE(B66),COLUMN(B1:F1)),ROW(
INDIRECT("1:"&COUNTA(B66)))),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!

 

[Steve Rolls]

Excellent ! Thank You all.

--
Steve

Try the following array formula...

=MIN(VLOOKUP(A6,A2:F4,SMALL(IF(B1:F1=TRANSPOSE(B66),COLUMN(B2:F2)),ROW(
INDIRECT("1:"&COUNTA(B66)))),0))

...confirmed with CONTROL+SHIFT+ENTER.

Hope this helps!

 

[Max]

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>)