# How to write a formula to find largest number?

In column A I have a list of names. In column B I have a list of dollar amounts that each name in column A earned. I would like a formula that can find the largest amount in column B and then display the name from column A of the person who earned that amount.

edit retag close merge delete

Sort by » oldest newest most voted

Hi

There is no need for macro or rearrange data. With Names in A and Amounts in B, to find first & second use formulas like:

=INDEX(A2:A100;MATCH(LARGE(B2:B100;1);B2:B100;0))
=INDEX(A2:A100;MATCH(LARGE(B2:B100;2);B2:B100;0))


Regards

more

Brilliant! Thank you for a valuable formula!

( 2015-06-28 11:47:58 +0100 )edit

If it works with your workflow, you could sort the contents of Cols A and B by the contents of B in descending order. Then just look at it. It's not elegant, it is manual, but it should work.

more

If you have the other way around (i.e. values in Col A and names in Col B) it is quite easy: in cell C1 use function =MAX(A1:A10) and in cell D1 use function =VLOOKUP(C1; A1:B10;2)

Explanation: The second function is looking for the MAX value (C1) and getting from the block A1:B10 the corresponding name from column 2. There is one limitation to this method: if there are two people who have the same value, the result will be the first on the list because the search is carried from top to bottom...

Hope this helps ;)

more

Can we add macros. i also need to find the second biggest number..

( 2014-10-01 07:28:28 +0100 )edit

We should also consider that the "largest" value may occur more than once. The maximum number of occurrences of the maximum value is the total size of the sample. Using a lookup based on the rank of a value with respect to a sample must therefore always thoroughly consider if it makes sense at all.

Another aspect should be the inefficiency of LARGE (or similar approaches) which may be of some importance in cases where many cells are meant to deliver information related to different ranks with respect to a large sample. The function has to examine the complete sample anew for every call.

Creating a sorted report (It may be placed anywhere by the 'Data' > 'Sort' tool, not overwriting the original data this way.) as @JKEngineer suggested would avoid both the mentioned problems.

A sorted output based on formuale should also not be less efficient than using LARGE for the complete range of ranks. And it would also avoid the issue of many equally ranking values and the erroneous lookup results caused by it.

more