If you haven’t read my post on “Excel Tip: SUM Top 5 values”, then this blog is an extension to that blog post. Do make sure to read it there because we will be using the same concept here in this tip.

Our dataset is simple as shown in figure below. We want to get the Max times for each city in a second list. How can we do this. The most easiest method I have seen is people resorting to PIVOT. If you want that implementation, do let me know and I will post that separately.

Just like in our previous post (Excel Tip: SUM Top 5 values), we used LARGE function there. Here we will use the MAX function with a conditional operator of IF. So in our example, we getting the MAX of values from the range B3:B8 where the value of D3 needs to be in the range A3:A8. Simple concept.

If you press a simple Enter you will get a value of 0. Remember the magic key (Ctrl+Shift+Enter) after you finish the formula and voila. You get exactly what you wanted. A typical output is below.

Now to copy this formula across E4 and E5. Find the cross arrow in E3 right hand- corner location. Now “Double Click” once you find the cross arrow.

Now that task automatically fills the range and you can see we have our desired output in less than a minute.

Hope you are enjoying the series of Excel Tips and tricks I am showing over the blogs. Do let me know of topics that will interest you and I will try to cover them here. Have a great day and learn something new every single day.