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 answers

  1.
    LePierre Posted messages 249 Status Member 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
    1. 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
    2. LePierre Posted messages 249 Status Member 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