How to look up a max value from an arry (on another sheet)

Why won't this formula work to look up the maximum value in a column on another sheet?

=VLOOKUP(MAX('J-A'!$AB$18:$AB$119),'J-A'!$AB$18:$AB$119,1)           This returns #VALUE.  (The subject column contains calculated values.  Does that present a problem?)


I tried using a simpler example on a list of simple numbers on the same sheet:

=VLOOKUP(MAX(J35:J47),J35:J47,1)         This always returns the last random number in the column
David_W_RAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

ProfessorJimJamCommented:
Is your data sorted?
0
ProfessorJimJamCommented:
Because you are using the approx match of vlookup
0
ProfessorJimJamCommented:
If the last argument of vlookup is missing then by default the match type is true which means that vlookup will do approx match and that will only work if your lookup array is sorted
0
Cloud Class® Course: Microsoft Office 2010

This course will introduce you to the interfaces and features of Microsoft Office 2010 Word, Excel, PowerPoint, Outlook, and Access. You will learn about the features that are shared between all products in the Office suite, as well as the new features that are product specific.

ProfessorJimJamCommented:
If you sort your lookup array range then your second formula should work
0
David_W_RAuthor Commented:
Thanks.  I'll try it now.
0
David_W_RAuthor Commented:
I added     ,0    to the end of the formula and it found the max value.   Great!   One step closer to a solution, but as the original question asked, why won't the first formula work when I add the same 4th argument?  Is is because the list is composed of calculated values also?  (calculated by vlookup statements)
0
ProfessorJimJamCommented:
True
0
ProfessorJimJamCommented:
Use the evaluate tool under formula tab and then step into the formula and then you will see why it does not work
0
David_W_RAuthor Commented:
"The cell currently being evaluated contains a constant."
0
Saurabh Singh TeotiaCommented:
Any particular reason you are not applying only this formula..??

=MAX('J-A'!$AB$18:$AB$119)

This will give you max value in return only what you are looking for..

Saurabh...
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
ProfessorJimJamCommented:
I am not sure if I understand. Did you use the evaluate formula tool ? Perhaps if you could attach a dummy file , then I would be able to tell what is causing that error
0
David_W_RAuthor Commented:
The simplification worked just fine, Saurabh.  Thanks for struggling with me and my  over-complicated question, Professor.  I'll put the 'Evaluate Formula' to good use next time also.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.