Max Value in a Hlook up

Question

I have a huge table in this format below that makes VLOOK up etc not possible. I would like to get from every month the highest value from the small sample like below.

2-2018  2-2018  2-2018  3-2018  3-2018  3-2018  3-2018  3-2018  3-2018  
   7      7       8       7       8       9       7       7      9

On an other slide I can the create a sheet were I then need to find back the month and correlate it to the table were it then find the highest value for that month:

   A     B
2-2018 = 8
3-2018 = 9

I want to know the MAX per month and have been working with MAX, INDEX and MATCH, but cannot get it to work.

Possible duplicate of [Conditional Min and Max in Excel 2010](https://stackoverflow.com/questions/11570223/conditional-min-and-max-in-excel-2010) — TomJohn, Feb 16 '18 at 21:17

Dude_Scott · Answer 1 · 2018-02-17T11:43:05.477

1

Try using a MAXIFS formula. For example, if the horizontal dates and numbers are in cells A1:Z2 and the vertical date your looking for the max of is in A4: formula in B4

=MAXIFS(A2:Z2,A1:Z1,A4)

Edit for versions less that 2016

You could also do a sumproduct for formula using arrays. Note: this is a volatile formula and could slow down the workbook is used in excess.

=SUMPRODUCT(MAX((A1:Z1=A4)*(A2:Z2)))

edited Feb 17 '18 at 11:43

answered Feb 16 '18 at 22:07

Dude_Scott

641
5
9

`MAXIFS` function was introduced in Excel 2016, so good answer, however may not work for some people :) – TomJohn Feb 17 '18 at 08:04
Ok ok. Went with a more dangerous approach – Dude_Scott Feb 17 '18 at 11:43
Don’t forget to mark as the answer if this worked for you – Dude_Scott Feb 18 '18 at 17:13

virtualdvid · Answer 2 · 2018-02-16T22:36:35.070

0

If you want to get a dynamic and deep analysis for your data you can use Power Pivot it is a free add-in for Excel 2010+ that you can download and activate easily. You can connect directly your data and use the query editor. There you will have a lot of tools to edit your data.

For your specific request follow these steps:

edited Feb 16 '18 at 22:36

answered Feb 16 '18 at 22:28

virtualdvid

2,323
3
14
32

Max Value in a Hlook up

2 Answers2