View original (French)

MAX of VLOOKUP Excel

Solved
Antoine -  
 Zeratz -
Bonjour,

I have a two-column table.
The first column contains values, some of which are identical, and the second column contains the corresponding numbers.
Using VLOOKUP, I find the corresponding number for my value in the first column.
My problem is that I'm looking for the maximum of these values:
If I have the value "PIERE" two times in my first column and the corresponding numbers are 11 and 15, I would like my function to return 15.
Unfortunately, VLOOKUP stops as soon as it finds an element in the first column...
How can I find the maximum of the corresponding numbers? I can't seem to combine it with a MAX function.

Thank you in advance,

Antoine
Configuration: Windows XP Internet Explorer 7.0

2 réponses

Réponse 1 / 2
LePierre Posted messages 249 Status Membre 338
 
Hello

set your values in A2:B21
enter the value to search in column 1 in F1
enter the formula in F2: =MAX(IF($F$1=A2:A21,B2:B21,""))
to be confirmed by pressing Ctrl Shift and Enter
as it is an array formula
and you will get the maximum sought
see you later
19
Antoine
 
Hi Lepierre,
Thank you for your response but unfortunately it's not working very well:
If the first value in my column A is the value in cell F1, then it simply gives me the MAX of column B
If the first value in my column A is different from the value in cell F1, it shows #VALUE!

Actually, if I have the matrix:

Pierre 12
Jaques 1000
Pierre 55
Paul 354

I would like that if I ask for Pierre it returns 55 (the max of Pierre only, not the whole)

Thanks if you have another idea
0
LePierre Posted messages 249 Status Membre 338 > Antoine
 
Hello

The formula works provided you validate it as an array formula!
Here is the example file:
https://www.cjoint.com/?eCrqrZzW8z

You will notice the { and } surrounding the formula in F2: {=MAX(IF($F$1=A2:A21,B2:B21,""))}
which are the signs of an array formula.
To proceed, select F2 and to validate, place the cursor in the formula bar and simultaneously press Ctrl Shift and Enter

See you later
0