Ask Your Question

How to write a formula to find largest number? [closed]

asked 2013-12-09 00:22:49 +0200

JDavid gravatar image

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 flag offensive reopen merge delete

Closed for the following reason the question is answered, right answer was accepted by Alex Kemp
close date 2020-08-21 23:59:44.006631

4 Answers

Sort by » oldest newest most voted

answered 2015-06-11 14:21:10 +0200

pierre-yves samyn gravatar image


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


See LargestNumber.ods


edit flag offensive delete link more


Brilliant! Thank you for a valuable formula!

Pedro gravatar imagePedro ( 2015-06-28 11:47:58 +0200 )edit

answered 2015-06-12 12:31:12 +0200

Lupp gravatar image

updated 2015-06-12 12:45:10 +0200

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.

edit flag offensive delete link more

answered 2015-06-11 13:57:20 +0200

JKEngineer gravatar image

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.

edit flag offensive delete link more

answered 2013-12-09 00:42:09 +0200

Pedro gravatar image

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 ;)

edit flag offensive delete link more


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

umarphysics gravatar imageumarphysics ( 2014-10-01 07:28:28 +0200 )edit

Question Tools

1 follower


Asked: 2013-12-09 00:22:49 +0200

Seen: 14,868 times

Last updated: Jun 12 '15