View original (French)

Search notes for students with id names.

Caroline -  
eriiic Posted messages 24581 Registration date   Status Contributor Last intervention   -
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 Posted messages 24581 Registration date   Status Contributor Last intervention   7 281
     
    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
  2. Vaucluse Posted messages 27336 Registration date   Status Contributor Last intervention   6 453
     
    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
  3. Raymond PENTIER Posted messages 58219 Registration date   Status Contributor Last intervention   17 485
     
    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