MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Combining MAX and OFFSET?

Posted by Eric on October 06, 2001 3:13 PM

Hi all, here is my situation:

Col. A

Col. B

Col. C (single cell)
Find the maximum value in col. B and display its corresponding string value from column A.

I have a pretty long list and I'm wondering if there are any efficient way to accomplish this? Thanks in advance!


Posted by Aladin Akyurek on October 06, 2001 3:25 PM

Hi Eric,

I think the following should be efficient enough.

=IF(COUNTIF(B:B,""&MAX(B:B))=1,INDEX(A:A,MATCH(MAX(B:B),B:B,0)),"More than 1 Max value")

Any comments?



Posted by Eric on October 06, 2001 5:02 PM

Thanks Aladin,

I took some time to figure out your formula and tried it but for some reason it keeps returning #VALUE! I've double checked my range and criterias but can't seem to correct the error. Any suggestions? Thanks again!

Posted by Eric on October 06, 2001 5:45 PM

Sorry, after going thru it more carefully I found my mistake and now it works perfectly. Thanks for your help!