# find min value depending on range

#### richardlit

##### New Member
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

### Excel Facts

Return population for a City
If you have a list of cities in A2:A100, use Data, Geography. Then =A2.Population and copy down.

#### richardlit

##### New Member
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

##### MrExcel MVP
In C2:

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

confirmed with Ctrl+Shift+Enter. Copy down.

#### richardlit

##### New Member
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

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

Replies
2
Views
37
Replies
5
Views
51
Replies
4
Views
31
Replies
4
Views
51
Replies
1
Views
39