find min value depending on range

[richardlit]

Hi

I need to find the minimum value in row 2 for each account in row 1, for example

row 1 row 2
111 50
222 20
333 80
444 40
111 40
222 50

Results would show 111 with a minimum value of 40, 222 with a minimum value of 20 etc etc

I would prefer a formula that does not use an array or VBA.

your help is greatly appreciated!

Thanks
Richard

 

[Andrew Poulsom]

If you don't want to use the array formula:

=MIN(IF(A2:A7=111,B2:B7))

you can use the DMIN function:

http://www.techonthenet.com/excel/formulas/dmin.php

or a pivot table.

 

[richardlit]

Thanks for the quick response!

I need the results to show in row 3 for each number. For example

row 1, row 2, row 3 (row 3 is the minimum value in row 2 where value in row 1 is the same)
111, 50, 40
222, 20, 20
333, 80, 80
444, 40, 40
111, 40, 40
222, 50, 20

Hope this makes sense.

Thanks
Richard

 

[Andrew Poulsom]

In C2:

=MIN(IF(A$2:A$7=A2,B$2:B$7))

confirmed with Ctrl+Shift+Enter. Copy down.

 

[richardlit]

Hi

This works but slows down my spreadsheet drastically (I'm not sure if this is because it is an array or the number of rows, currently around 80,000).

Is there a different formula e.g. IF statement or INDEX MATCH function?

Thanks
Richard

 

[Andrew Poulsom]

Sorry, I don't know how to return a conditional MIN without an array formula.