How to extract specific hourly rows based on the maximum daily value in a column

[auato]

Hi,
Very often I have to face a similar scenario described below:
a table with days and hours and then all the values in the next columns from which I need to extract only some rows... precisely, those ones with the maximum value in a column. In other words, the outcome is composed of one row per day that represent the hour with the maximum value of a specific column... and of course that selected row has all the corresponding values for that hour.

I found a way to cope this scenario but I have to implement two pivot tables and a couple of functions (CONCATENATE and VLOOKUP). I am curious to know how you resolve this problem and if someone of you has a smarter or faster or easiers solution to resolve that scenario. I attach the Excel file with my solution and some comments as well.

Thank you for your attention.

[MarvinP]

Hi auato and welcome to the forum,

Here is the formula that will replace your "X" and two Pivot Tables.

Please Login or Register  to view this content.

See it in the attached file below.
Index Match from Maxif Table.xlsx
Wash you hands and don't touch you face from Seattle.

[Pepe Le Mokko]

I don't think MAXIFS was available in XL 2016?

[MarvinP]

I guess the OP needs to test it
https://corporatefinanceinstitute.co...tion-in-excel/
claims it was I also find that lots of people upgrade their versions and don't update their profile. So what they show they have and what they really have are different. You might be right . I hope auato gets back to us and says if it works or not.

[Pepe Le Mokko]

I also find that lots of people upgrade their versions and don't update their profile

When I see an old member with a 2003 version in his profile, I post a request to adapt.(which works 99% of the time)
Further than that, we can't ask everyone to double-check.
That " canned reply" also includes a sentence " ...members tailor their answer to your version.."......

[auato]

Thanks Marvin!
Yes, Pepe Le Mokko is right (thank you Pepe) MAXIFS doesn't exist in my Excel 2016.
But, as I was too curious about your solution I tried it on another laptop of a friend of mine... and, yes it works perfectly!
Unfortunately my company still adopts the old Office2016.

[auato]

OK, a workaround could be:

Please Login or Register  to view this content.

But I forgot to explain a strange point.... when I open my Excel 2016, even though MAXIFS is not recognized, I noticed that your formula worked fine filling correctly the cells... and when I look into the cell I can see that unknown part in blue:

Please Login or Register  to view this content.

[MarvinP]

Hi auato,

Thanks for the feedback. I'm not an expert on what functions are/were in which version of Excel I have no idea why MaxIfS should work in older versions or why your blue above is there. I hope this problem is solved, or do you want more work on it? There is a Control+Shift+Enter (CSE) solution (these are called Array Formulas) for your old versions if you still need it. See:
https://www.excelforum.com/excel-for...xcel-help.html
It looks like I've been schooled on this topic before and I even asked the question.

[Pepe Le Mokko]

AFAIK the "xlfn" string indicates that the function following it is not native to the XL version in use.
BTA, if it works, who cares?
The non array alternatives would imply using AGGREGATE or SUMPRODUCT the latter being rather cumbersome in this case.

Se va bene per Lei, anche per noi !

[MarvinP]

Hey Pepe,

This site claims

which is not supported in versions of Excel earlier than Excel 2007.

https://support.office.com/en-us/art...4-9FBB77FD5025

Here is another interesting result when the client copy won't work but opening it using OneDrive does?
https://answers.microsoft.com/en-us/...b-94478ab72f50

[auato]

Perfect, Marvin & Pepe, per me va bene, anzi benissimo :-)
Thank you very much for your precious support. I have enriched my skills on Excel with MAXIFS or the was MAX(IF) and AGGREGATE.... further these also enreached myself with CSE for Array Formuals which I am already using a lot .... and learned another trick to handle those type of tables I depicted above. Thank you very much guys! Really appreciated your generosity. See you next time

[MarvinP]

Hey auato,

Thanks for the nice words. It is always nice to know people are learning from what we suggest.

Could you edit the very first post and change the Prefix to "Solved". That way we feel confident you got a good answer. It also allows us to help others without opening this thread again to see if you need more help. Also, if you find an answer especially good you can click on the "Add Reputation Star" below the answer, as that is this site's method to track better answers.