Forum Discussion

heyaburt's avatar
heyaburt
Copper Contributor
May 11, 2017
Solved

MATCH or INDEX function

I have a spreadsheet with two columns labeled STUDENT IDs and STUDENT NAMES.

I want to create a MATCH or INDEX formula in a block so when I input the student ID in one cell, the coresponding student name comes up in the cell beside it.

 

for example: I want the student name to come up in cell B15 when I input their student ID # in cell A15.  the student ID #'s are contained in cells A2 thru A11 and are named StudentId.  The Student names are contained in cells B2 thru B11.

 

I can get the student ID #'s to populate when I input their names but not vice versa.

 

Thanks!

Mike

Formulas & Functions
Need Help
  • Yury Tokarev's avatar
    Yury Tokarev
    May 12, 2017

    Hi Mike,

     

    you can use the following formulas

     

    For Student Name (B15)

    =INDEX($B2:$B11,MATCH($A15,$A$2:$A$11,0))

     

    For Overall Score (C15)

    =INDEX($S2:$S11,MATCH($A15,$A$2:$A$11,0))

     

    For Final Grade (D15)

    =INDEX($T2:$T11,MATCH($A15,$A$2:$A$11,0))

     

    Hope this helps

    Thanks

    Yury

     

5 Replies

Sort By
  • Yury Tokarev's avatar
    Yury Tokarev
    Steel Contributor
    May 12, 2017

    Hi Mike,

     

    you can use the following formulas

     

    For Student Name (B15)

    =INDEX($B2:$B11,MATCH($A15,$A$2:$A$11,0))

     

    For Overall Score (C15)

    =INDEX($S2:$S11,MATCH($A15,$A$2:$A$11,0))

     

    For Final Grade (D15)

    =INDEX($T2:$T11,MATCH($A15,$A$2:$A$11,0))

     

    Hope this helps

    Thanks

    Yury

     

    • heyaburt's avatar
      heyaburt
      Copper Contributor
      May 12, 2017
      Thank you, Yury. It helps out quite a bit!

      I appreciate it!

      Mike
  • Wyn Hopkins's avatar
    Wyn Hopkins
    MVP
    May 12, 2017

    Hi heyaburt

     

    In cell B15   enter this...     =INDEX (B2:B11,  MATCH(  $A15,   $A2:$A11,  0) )

     

    Then when you type the number 1 in cell A15 the value in B15 should show BAM BAM.

     

    One thing to make sure is that the number you type into A15 is the same format as the cells A2:A11.   So if they have been entered as numbers or text in A2:A11  then A15 must also be number or text format.

     

    You can then copy this formula to the other cells in row 15 and just change the INDEX reference from B2: B11  to whichever other column you want to bring back

     

    Cheers

     

    Wyn

    • heyaburt's avatar
      heyaburt
      Copper Contributor
      May 12, 2017
      Hello Wyn,

      Thank you for quick response. My wife was able to figure it out using just the INDEX function:

      =INDEX(A2:B11,A15,2)

      It seems to work just fine this way too. Any drawbacks to using it just this way?

      Thanks again!
      Mike
      • Wyn Hopkins's avatar
        Wyn Hopkins
        MVP
        May 12, 2017
        Hi Mike

        The limitation is that it will pull back the student in row 1, row 2 etc. Because the student number is 1 and 2 etc. If the student number was 10 but they were in the 12th row of the table you would get the wrong data

Resources