select min value in B column for same values in A columns excel?

Question

I would like to get e.g. min or max value from column2 for same value in column1. In mine example I want max value from column2 for value A (column1) and that is 18, and for B is 27.

I've tried with array function but I cannot manage it to work properly.

Tnx for any tip or solution.

Example:

Column1 Column2 Max_value

A       1       18
A       5       18
A       18      18
A       3       18
A       4       18
B       2       27
B       5       27
B       18      27
B       27      27
B       5       27
B       3       27

Sorry for poor example, but I couldn't post picture, my rank is not high enough :)

Possible duplicate of [Excel: Find min/max values in a column among those matched from another column](http://stackoverflow.com/questions/20904092/excel-find-min-max-values-in-a-column-among-those-matched-from-another-column) — sancho.s ReinstateMonicaCellio, Feb 04 '17 at 11:59

score 2 · Accepted Answer · answered Feb 14 '13 at 16:43

2

The standard "array formula" is like this for C2

=MAX(IF(A$2:A$100=A2,B$2:B$100))

confirmed with CTRL+SHIFT+ENTER and copied down column - adjust ranges as required

replace MAX with MIN to get the minimum values in the next column

answered Feb 14 '13 at 16:43

barry houdini

45,615
8
63
81

score 2 · Answer 2 · answered Feb 14 '13 at 17:13

2

This is a cinch with pivot tables. Here's one way to do it.

Excel pivot table

answered Feb 14 '13 at 17:13

rajah9

11,645
5
44
57

Nice coding-free approach! – Maks Gordeev Feb 14 '13 at 17:35

select min value in B column for same values in A columns excel?

2 Answers2

Linked