Forum Discussion

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 stude...

Formulas & Functions

Need Help

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

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

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
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