MAX Value Help

[kzoocheme]

I have worksheets that are labeled A-Q, all with data that is formatted the same. On worksheet R, I have column A with IDs. I want to find the max value in Column L across worksheets A-Q by matching the IDs in columns A of A-Q to column A on worksheet R. Is that feasible? Feel like MAX(IF) would work, but I am not having any success.

 

[Eric W]

3-D formulas (formulas involving more than 1 sheet) are particularly tricky in Excel. There are only a few functions that allow that. For example, if you just want the maximum value in column D of sheets A:Q you could use:

=MAX('A:Q'!L:L)

But even this isn't perfect - it assumes all the sheets are in order, and that there are no other sheets in the middle.

And if you want to use some kind of IF as part of that? Forget it!

There are various tricks using array formulas, OFFSET, INDIRECT, SUBTOTAL, and so on, but I am not aware of any way that these will work for you. The best I could come up with is:

=MAX((A!K1:K5=D!K1:K5)*A!L1:L5,(B!K1:K5=D!K1:K5)*B!L1:L5,('C'!K1:K5=D!K1:K5)*'C'!L1:L5)
confirmed with Control+Shift+Enter.

You'd have to repeat each of the colored sections for each of your sheets, so it would end up as a long formula. You could put the individual sheet formula on the actual sheet, say in Z1, then use =MAX('A:Q'!Z1) to get the final result.

Hope this gives you some ideas.