find max value based on criteria

1. find max value based on criteria

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??

Hein

2. 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,""))

3. Hi Dave

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

4. 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

5. 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

6. Hein,

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

7. 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

8. Thanks Sweep and Charlie!!

It has been a good learning experience for me!

Hein

CC

There are currently 1 users browsing this thread. (0 members and 1 guests)

Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1