find max value based on criteria

**Hein** · 11-27-2008, 10:35 AM

Hi all

I have tried to combine a vlookup function with a max function, without succes

Let me explain
I have 2 columns, 1 with descriptions, 1 with values

On another place I have a summary of the descriptions. Next to this description I want to have the max value from the 2nd column described above

(My demo makes hopefully more sence than my description)

How can I get this result??

Thanks in advance
Hein

**sweep** · 11-27-2008, 10:43 AM

Hi,

In your example, the following array will give the desired result

=MAX(IF(C7:C12=G7,D7:D12,""))

....confirm this with CTRL, SHIFT and ENTER, excel will display it like this

{=MAX(IF(C7:C12=G7,D7:D12,""))}

=MAX(IF(range_of_labels=required_label,range_of_values,""))

**Hein** · 11-27-2008, 10:54 AM

Hi Dave
Thanks for your input

It works if there is only "apple".
In my example I should have added more details, sorry for that

What happens if in column D more fruit is added, eg pear, cherry etc, with their values in column D (there will be more than 1 pear etc)

If I also add these fruits to column G, the max value stays the same, for every fruit it is the same value)

How can I solve this

Thanks Hein

PS
Once again sorry for not being complete

**Cheeky Charlie** · 11-27-2008, 11:20 AM

The formula given by Sweep works perfectly.

Make sure the ranges in the formula are big enough and make sure you have entered it with Ctrl+shift+enter as described.

Change G7 as appropriate.

It would not be tricky to copy this formula further but if your real situation is more complex then I would recommend a pivot table.

CC

**Hein** · 11-28-2008, 04:33 AM

Thanks all for your input
The reason why it didn't work after the 1st try was that I forgot the ctrl-shft-enter combination!

Last question (if permitted): what are the effects of this key-combination.
In other words: when can I use this and why is it used?

Thanks
Hein

**sweep** · 11-28-2008, 05:24 AM

Hein,

This is best explained by a true expert. Take a look here: http://www.cpearson.com/excel/ArrayFormulas.aspx

**Cheeky Charlie** · 11-28-2008, 05:25 AM

It creates what's called an array formula, effectively this tells Excel "where you expect to see one value in this formula, I have actually put a whole range, please perform the calculation on every item in the range"

If you go to Tools-> formula auditing ->evaluate formula you will understand much more clearly. (try this on a small array size so you can understand what's going on)

CC