# Vlookup function

#### jakebb

##### New Member
Given a table of student information, lookup and display all the marks for the student corresponding tothe student ID provided. When the student ID is changed to that of another student, the marks shouldupdate accordingly. With exact same formula repeated in all thecells in the range C8:C14. In other words, the formula should use the item to its left to lookup theinformation. Named ranges are required for this part. They are already defined for you

 StudentID: 258600 Student Information Found: Name Assignment 1 Assignment 2 Midterm Project Exam Grade

<colgroup><col><col></colgroup><tbody>
</tbody>

 StudentID Name Assignment 1 Assignment 2 Midterm Project Exam Grade 252549 Chad 68 73 77 93 85 82.1% 318312 Durant 71 74 66 81 66 70.3% 295160 Elly 31 56 47 47 42 44.3% 297061 Jordan 42 65 50 60 71 61.1% 258600 Lea 48 70 73 69 60 64.2% 287519 Rolex 58 62 43 55 62 56.4% 327862 Shawlty 74 62 57 71 67 66.0% 326873 Steven 53 42 45 68 47 50.9% 334166 Ali 70 75 81 83 77 78.1% 331347 Twanay 38 51 62 42 53 50.9%

<colgroup><col><col><col span="2"><col span="4"></colgroup><tbody>
</tbody>

### Excel Facts

Which came first: VisiCalc or Lotus 1-2-3?
Dan Bricklin and Bob Frankston debuted VisiCalc in 1979 as a Visible Calculator. Lotus 1-2-3 debuted in the early 1980's, from Mitch Kapor.

#### Anand Sharma

##### Board Regular
Hi

Instead of using vlookup, use index-match here as below

 StudentID: 318312 Student Information Found: Name Durant Assignment 1 INDEX(A14:H23,MATCH(B1,A14:A23,0),3) Assignment 2 INDEX(A14:H23,MATCH(B1,A14:A23,0),4) Midterm INDEX(A14:H23,MATCH(B1,A14:A23,0),5) Project INDEX(A14:H23,MATCH(B1,A14:A23,0),6) Exam INDEX(A14:H23,MATCH(B1,A14:A23,0),7) Grade INDEX(A14:H23,MATCH(B1,A14:A23,0),8) StudentID Name Assignment 1 Assignment 2 Midterm Project Exam Grade 252549 Chad 68 73 77 93 85 82.10% 318312 Durant 71 74 66 81 66 70.30% 295160 Elly 31 56 47 47 42 44.30% 297061 Jordan 42 65 50 60 71 61.10% 258600 Lea 48 70 73 69 60 64.20% 287519 Rolex 58 62 43 55 62 56.40% 327862 Shawlty 74 62 57 71 67 66.00% 326873 Steven 53 42 45 68 47 50.90% 334166 Ali 70 75 81 83 77 78.10% 331347 Twanay 38 51 62 42 53 50.90%

<colgroup><col><col><col span="2"><col><col><col><col></colgroup><tbody>
</tbody>

#### alansidman

##### Well-known Member
 v C D E F G H I J 7 StudentID: 258600 8 9 Student Information Found: 10 Name Lea =VLOOKUP(\$D\$7,\$C\$20:\$J\$29,2,0) 11 Assignment 1 48 =VLOOKUP(\$D\$7,\$C\$20:\$J\$29,3,0) 12 Assignment 2 70 =VLOOKUP(\$D\$7,\$C\$20:\$J\$29,4,0) 13 Midterm 73 =VLOOKUP(\$D\$7,\$C\$20:\$J\$29,5,0) 14 Project 69 =VLOOKUP(\$D\$7,\$C\$20:\$J\$29,6,0) 15 Exam 60 =VLOOKUP(\$D\$7,\$C\$20:\$J\$29,7,0) 16 Grade 64.20% =VLOOKUP(\$D\$7,\$C\$20:\$J\$29,8,0) 17 18 19 StudentID Name Assignment 1 Assignment 2 Midterm Project Exam Grade 20 252549 Chad 68 73 77 93 85 82.10% 21 318312 Durant 71 74 66 81 66 70.30% 22 295160 Elly 31 56 47 47 42 44.30% 23 297061 Jordan 42 65 50 60 71 61.10% 24 258600 Lea 48 70 73 69 60 64.20% 25 287519 Rolex 58 62 43 55 62 56.40% 26 327862 Shawlty 74 62 57 71 67 66.00% 27 326873 Steven 53 42 45 68 47 50.90% 28 334166 Ali 70 75 81 83 77 78.10% 29 331347 Twanay 38 51 62 42 53 50.90%

#### jakebb

##### New Member
IS there anyway that the last part of the formula like 3,4,5.. could be the same, so it is the EXACT same formula?

#### alansidman

##### Well-known Member
the simple answer is no as this determines which column to get the data from.

#### Tetra201

##### MrExcel MVP
IS there anyway that the last part of the formula like 3,4,5.. could be the same, so it is the EXACT same formula?
There is.

Using alansidman's setup, put the following formula in cell D10 and copy down:

=VLOOKUP(\$D\$7,\$C\$20:\$J\$29,MATCH(\$C10,\$C\$19:\$J\$19,0),0)

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

1,167,747
Messages
5,855,436
Members
431,732
Latest member
albertdi827

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back