Combonation of ADDRESS, MATCH and MAX not working

I'm trying to combine three functions: ADDRESS, MATCH, MAX. In the attached workbook I find nothing wrong with the formula but I am not getting the correct cell reference?
Frank FreeseAsked:
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.

Saqib Husain, SyedEngineerCommented:


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
Saqib Husain, SyedEngineerCommented:
This is because the address function needs the absolute row number whereas the match function is returning the row number relative to row 2. Change it to row 1 as my formula suggests
Frank FreeseAuthor Commented:
good catch -
JavaScript Best Practices

Save hours in development time and avoid common mistakes by learning the best practices to use for JavaScript.

Frank FreeseAuthor Commented:
thank you so very much
barry houdiniCommented:
As written you may get incorrect results with that formula - e.g. if you change A3 to 1000 the formula should now return A3 but it returns A11.

You need a 3rd argument of zero in the MATCH function in order to get the correct result in all circumstances, i.e.


I'm interested why you want to find the address, though? Is that your final goal or are you intending to use the address returned by that formula to calculate something else? If it's the latter then it's almost always not the best way to proceed because the address is a text value that you will need to convert back to a cell reference...

regards, barry
Frank FreeseAuthor Commented:
First, the workbook I am creating is designed for my youngest son who is looking for a tool to help him understand Excel formulas and functions. The worksheet is just an example of combining several functions in one formula - no hidden agenda - just a teaching tool.
Next, I did notice where the formula I accepted, gave me an incorrect answer when I changed a cell. I appreciate you noticing the needed change - let me try it and get back with you. I have no points to give except thanks.
Just incorporated your changes and it is working as I was expecting.
Thanks, Barry. Very much
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.