CODEWITHSUNDEEP
excel
imp
imp

Reputation: 470

Select column to the left of max in a row

I want to be able to select the column to the left of the column that is the max in a row. Basically, I have the text there, so it would be something like this:

0  A           B
1  Tomatoes:   10
2  Apples:     5
3  Bananas:    8

"select column to the left of the max of b1 to b3" I know it's max(b1:b3) to get the max, but how do I select the max? By the way, this is how I'm going to set it up:

There are the most of: "Tomatoes"

Should be the answer, not 10, which it would normally return if I'd just use the max formula.

Upvotes: 0

Views: 2063

Answers (1)

Jook
Jook

Reputation: 4682

This would be my suggestion for this problem:

=INDEX(A1:A3,MATCH(MAX(B1:B3),B1:B3,0))

You get the MAX value, then you get the row of it using MATCH, then using INDEX you select the right value from column A.

Ok, this here would also work, but only, if your column B is sorted ascending or descending.

=LOOKUP(MAX(B1:B3),B1:B3,A1:A3)

You could also do VLOOKUP, but then you would have to switch col A with B, because you will search in the first column and lookup in a higher one.

Have you thought about, what to do, if there are two max-values?!

Upvotes: 1

Related Questions