View original (French)

Search notes for students with id names.

Caroline -  
Hello,

I have a sheet where I have a list of students by first and last name with the grades in different subjects and the average of the grades in columns,

I am trying to find the average for a few students using VLOOKUP, but the problem is that it gives me the grade of the first student with the same name at the top of the list.

Is there a way to use both first and last names to remedy this problem?

Thank you in advance.

3 answers

  1.  eriiic Contributor
    Hello,

    what if you have 2 Paul Martin?
    You need to use a unique identifier like the student number.
    eric

    --
    By continuously trying, we eventually succeed.
    So the more it fails, the more chances we have that it works. (the Shadoks)
    In addition to the thank you (yes, it happens!!!), remember to mark it as resolved. Thank you
    1
    1. Vaucluse Contributor
      Hello
      with an array formula that needs to be entered by pressing enter while holding down the ctrl and shift keys
      (it should automatically appear in curly braces in the formula bar)
      principle
      =INDEX(field to extract,MATCH(cell last name&cell first name,field last name&field first name,0))
      for example
      list of last names in A1:A100 first names in B1:B100 value to extract in C1:C100
      last name sought in D1, first name sought in E1
      in F1:

      =INDEX($C$1:$C$100,MATCH(D1&E1,$A$1:$A$100&$B$1:$B$100,0))
      attention, quite a heavy formula, be sure to limit the number of rows to the necessary height

      or another simpler solution
      in your database, create a column, for example in Z1:Z100 with this formula, to drag down
      =A1&" "&B1
      which will combine last name and first name
      and thus in E1 the formula, this time not array :

      =INDEX($C$1:$C$100,MATCH(D1&" "&E1,$Z$1:$Z$100,0))

      and to finish the simplest way would then be to place in D1 a dropdown menu based on Z1:Z100 to select the last name and first name in the same cell, which would give in E1:

      =INDEX($C$1:$C$100,MATCH(D1,$Z$1:$Z$100,0))

      best regards

      0
      1. Caroline
        Thank you so much.
        0
    2. Raymond PENTIER Contributor
      Otherwise, you do like me:
      If the names are in column A and the first names in B, you insert a column in C and put the formula =A:A&" "&B:B, and you do your VLOOKUP from this column C.

      --
      Retirement is nice! Especially in the Antilles...
      Raymond (INSA, AFPA)
      0