CODEWITHSUNDEEP
excel
Seek Php
Seek Php

Reputation: 163

How to get max and min value with other column's values in excel?

I want to get max and min values with other column's values in ms excel 2007. I have two columns

| Column A | Column B |
-----------------------
|A         |   18     |
-----------------------
|B         |   78     |
-----------------------
|c         |   9      |

I want to print out max values and min values of "Column B" with "Column A" values. Please suggest me some formula.

Upvotes: 3

Views: 13106

Answers (2)

Mischinab
Mischinab

Reputation: 2911

The following formula will find the max value in column B, and return the value from column A that is in the same row:

=INDEX(A:A,(MATCH(MAX(B:B),B:B,0)))

In your example, the result would be "B".

Note that if the max value occurs in column B more than once, only the first one will be processed with this formula.

For minimum, just substitute MIN in place of MAX.

Upvotes: 3

lori_m
lori_m

Reputation: 5567

One way: select a cell to output the data to e.g. D2. Then choose

Data > Consolidate...Function:Min, Reference:$A:$B, Use labels in: Left Column

and repeat with Function: Max.

Another way: use a pivot table.

Upvotes: 0

Related Questions