Vlookup() to return smallest value in a range ?

[jxb]

Is there a way of returning the smallest value using some sort vlookup()?
Consider the following (see xls picture);
I am looking at a cell where the user selects which ID if wants the smallest values for (in this example min B2:D4)
I guess I am looking at way of have a dynamic range in the vlookup(). Maybe a need for Index or sometign along this line ?

Thanks
John

 

[kevin9999]

Book1
	A	B	C	D
1	ID	Value 1	Value 2	Value 3
2	1	1	2	3
3	1	4	5	6
4	1	5	0.5	2.5
5	2	1	2	3
6	2	4	5	6
7	2	5	0.25	2.5
8
9	User Choice =	1
10	min value =	0.5
Sheet3

Cell Formulas
Range	Formula
B10	B10	=MIN(IF(A2:A7=B9,B2:D7))

 

[jxb]

Thanks. If I try I get: #VALUE!

 

[kevin9999]

Try entering as an array formula - press Ctrl+Shift+Enter

Book1
	A	B	C	D
1	ID	Value 1	Value 2	Value 3
2	1	1	2	3
3	1	4	5	6
4	1	5	0.5	2.5
5	2	1	2	3
6	2	4	5	6
7	2	5	0.25	2.5
8
9	User Choice =	1
10	min value =	0.5
Sheet3

Cell Formulas
Range	Formula
B10	B10	=MIN(IF(A2:A7=B9,B2:D7))
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[Fluff]

Another option that doesn't need array entry

=AGGREGATE(15,6,B2:D7/(A2:A7=B9),1)

 

[jxb]

Another option that doesn't need array entry

=AGGREGATE(15,6,B2:D7/(A2:A7=B9),1)

sorry missed your reply when I ticked the array-way as a solution. Never used AGGREATE() so will look into it. Thanks for the input

 

[Fluff]

Glad we could help & thanks for the feedback.