# Solved Max function in more than one cell.

October 1, 2014 at 06:37:58
Specs: Windows 7
 I have used the Max function to obtain the maximum value in a column, however if there is more than one cell with a maximum how can I return a value of 0

See More: Max function in more than one cell.

✔ Best Answer
October 1, 2014 at 14:26:47
 WHEN NO VALUES IN THE CELLS IT RETURNS #N/A This will return the "No Winners This Week" message, OK?=IF(COUNTIF(AG1:AG10,MAX(AG1:AG10))<>1,"No Winners this Week",INDEX(C1:C10,MATCH(MAX(AG1:AG10),AG1:AG10,0)))EDIT ADDED:This one is a bit longer and will simply blank out the cell=IF(COUNT(AG1:AG10)=0,"",IF(COUNTIF(\$AG\$1:\$AG\$10,MAX(\$AG\$1:\$AG\$10))<>1,"No Winners this Week",INDEX(\$C\$1:\$C\$10,MATCH(MAX(\$AG\$1:\$AG\$10),\$AG\$1:\$AG\$10,0))))MIKEmessage edited by mmcconaghy

#1
October 1, 2014 at 07:12:27
 Try this, with your Data in Column A, cells 1 thru 10, like:``` A 1) 12 2) 74 3) 94 <-- 4) 83 5) 92 6) 31 7) 31 8) 94 <-- 9) 8 10) 14 ```In cell A11 enter this formula: =IF(COUNTIF(A1:A10,MAX(A1:A10))>1,0,MAX(A1:A10))You should get a Zero, change one of the 94's to 92and you will get the MAX value which is 94.Is that what your looking for?MIKEhttp://www.skeptic.com/

Report •

#2
October 1, 2014 at 09:45:36
 Thanks Mike, works fine.To follow on from here,In the spreadsheet, column A has a series of numbers in the cells. From the maximum value in that column i then wish to display the corresponding cell from column B, which is a list of names, in cell C1

Report •

#3
October 1, 2014 at 10:26:43
 With your data like:``` A B 1) 12 Lilian Delsignore 2) 74 Norris Kaminsky 3) 94 Shavonda Mccook 4) 83 Sharmaine Nardone 5) 92 Raven Hendon 6) 31 Wilfredo Hardiman 7) 31 Nenita Raffaele 8) 96 Soila Thrower 9) 8 Claude Hubbell 10) 14 Margo Leahy ```In cell C1 enter the formula:=IF(COUNTIF(A1:A10,MAX(A1:A10))=1,VLOOKUP(MAX(A1:A10),A1:B10,2,0),"Multi Max Value")You should get the name: Soila ThrowerYou did not specify what to do if there were multiple instances of the MAX value so, if there is more than one instance of the MAX value, you get the message "Multi Max Value". Change this to suit your preference.MIKEhttp://www.skeptic.com/

Report •

Related Solutions

#4
October 1, 2014 at 11:24:54
 That works however to simplify matters i stated columns A and B in my initial question but in my actual spreadsheet column AG contains the numbers and column C the names with the columns in between containing various values. I changed the relative cells in the function however it only shows a value as #N/A

Report •

#5
October 1, 2014 at 11:29:52
 the function however it only shows a value as #N/AThat's because the VLOOKUP() function ONLY get data that is to it's RIGHTSo you can not have the Numbers in AG and get the name from Column Cusing a VLOOKUP()If you had given that information in your original post, it would have made life so much simpler.If you can swap the columns, then the formulas should work.MIKEhttp://www.skeptic.com/

Report •

#6
October 1, 2014 at 12:03:29
 I cant change the columns around and I don't have sufficient knowledge to know exactly what information you need but its ok, I'll work through it. Thanks anyway

Report •

#7
October 1, 2014 at 12:08:31
 Try this, with your data like:``` C AG 1) Lilian Delsignore 12 2) Norris Kaminsky 74 3) Shavonda Mccook 94 4) Sharmaine Nardone 83 5) Raven Hendon 92 6) Wilfredo Hardiman 31 7) Nenita Raffaele 31 8) Soila Thrower 96 9) Claude Hubbell 8 10) Margo Leahy 14 ```Enter this formula:=INDEX(C1:C10,MATCH(MAX(AG1:AG10),AG1:AG10,0))You should get the name: Soila ThrowerYou still have not not specifed what to do if there were multiple instances of the MAX value?With this formula if there are multiple instances, you will only get the First Instance.MIKEhttp://www.skeptic.com/

Report •

#8
October 1, 2014 at 12:43:16
 This is a much more robust formula, so with your data like this:``` C AG 1) Lilian Delsignore 12 2) Norris Kaminsky 74 3) Shavonda Mccook 96 <-- 4) Sharmaine Nardone 83 5) Raven Hendon 92 6) Wilfredo Hardiman 31 7) Nenita Raffaele 31 8) Soila Thrower 96 <-- 9) Claude Hubbell 8 10) Margo Leahy 14 ```If there are multiple instances of the same MAX value this will return each name.This is an ARRAY formula, and as such it MUST be enteredusing the key combination of CTRL-SHIFT-ENTER.=IFERROR(INDEX(\$C\$1:\$C\$10,SMALL(IF(MAX(\$AG\$1:\$AG\$10)=\$AG\$1:\$AG\$10,ROW(\$AG\$1:\$AG\$10),""),ROW())),"")After you enter the formula, using CTRL-SHIFT-ENTER the formula should appear with curely brackets around it like this:{=IFERROR(INDEX(\$C\$1:\$C\$10,SMALL(IF(MAX(\$AG\$1:\$AG\$10)=\$AG\$1:\$AG\$10,ROW(\$AG\$1:\$AG\$10),""),ROW())),"")}You DO NOT put them there, Excel will do it if you have entered it correctly.Once you have entered the formula, drag it down several rows,to accommodate each new name that is matched.You should have the two names: Shavonda Mccook& Soila Throwereach in it's own cell.See how that works for you.MIKEhttp://www.skeptic.com/

Report •

#9
October 1, 2014 at 13:22:11
 If there are multiple instances of the MAX value then message "No winners this week"

Report •

#10
October 1, 2014 at 13:33:47
 Try this one:=IF(COUNTIF(AG1:AG10,MAX(AG1:AG10))>1,"No Winners this Week",INDEX(C1:C10,MATCH(MAX(AG1:AG10),AG1:AG10,0)))MIKEhttp://www.skeptic.com/

Report •

#11
October 1, 2014 at 14:10:29
 THAT WORKS BUT WHEN NO VALUES IN THE CELLS IT RETURNS #N/A

Report •

#12
October 1, 2014 at 14:26:47
✔ Best Answer
 WHEN NO VALUES IN THE CELLS IT RETURNS #N/A This will return the "No Winners This Week" message, OK?=IF(COUNTIF(AG1:AG10,MAX(AG1:AG10))<>1,"No Winners this Week",INDEX(C1:C10,MATCH(MAX(AG1:AG10),AG1:AG10,0)))EDIT ADDED:This one is a bit longer and will simply blank out the cell=IF(COUNT(AG1:AG10)=0,"",IF(COUNTIF(\$AG\$1:\$AG\$10,MAX(\$AG\$1:\$AG\$10))<>1,"No Winners this Week",INDEX(\$C\$1:\$C\$10,MATCH(MAX(\$AG\$1:\$AG\$10),\$AG\$1:\$AG\$10,0))))MIKEmessage edited by mmcconaghy

Report •

#13
October 1, 2014 at 14:48:46
 All good now, thanks Mike and thank you for all your help and patience.Chris

Report •