Finding a minimum value and selecting the cell containing this val

K

Kokomojo

Hi,
I have data in 2 ranges. I want to find the minimim value in these ranges
and have Excel then select the cell matching that value.

Here's my MIN function:
=MIN(B4:D8,B13:D17)

The result is located in C14. How do I get Excel to run this function, then
go and select C14?

Thanks.
 
R

Ron Coderre

If you want the address of the cell containing the minimum value in the
referenced ranges, try this:

A1:
=ADDRESS(MAX(SUMPRODUCT((B4:D8=MIN(B4:D8,B13:D17))*ROW(B4:D8)),SUMPRODUCT((B13:D17=MIN(B4:D8,B13:D17))*ROW(B13:D17))),MAX(SUMPRODUCT((B4:D8=MIN(B4:D8,B13:D17))*COLUMN(B4:D8)),SUMPRODUCT((B13:D17=MIN(B4:D8,B13:D17))*COLUMN(B13:D17))))

or
This might be a bit easier to follow:
A1: =MIN(B4:D8,B13:D17)
B1:
=ADDRESS(MAX(SUMPRODUCT((B4:D8=A1)*ROW(B4:D8)),SUMPRODUCT((B13:D17=A1)*ROW(B13:D17))),MAX(SUMPRODUCT((B4:D8=A1)*COLUMN(B4:D8)),SUMPRODUCT((B13:D17=A1)*COLUMN(B13:D17))))


Does that help?

***********
Regards,
Ron

XL2002, WinXP-Pro
 
R

Ron Coderre

Thanks for the feedback.....I'm glad that worked for you.


***********
Regards,
Ron

XL2002, WinXP-Pro
 
Top