Vlookup function

jakebb

New Member
Joined
Dec 2, 2016
Messages
2
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>

StudentIDNameAssignment 1Assignment 2MidtermProjectExamGrade
252549Chad687377938582.1%
318312Durant717466816670.3%
295160Elly315647474244.3%
297061Jordan426550607161.1%
258600Lea487073696064.2%
287519Rolex586243556256.4%
327862Shawlty746257716766.0%
326873Steven534245684750.9%
334166Ali707581837778.1%
331347Twanay385162425350.9%

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

Excel Facts

Test for Multiple Conditions in IF?
Use AND(test, test, test, test) or OR(test, test, test, ...) as the logical_test argument of IF.

Anand Sharma

Board Regular
Joined
Nov 22, 2016
Messages
63
Hi

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

StudentID:318312
Student Information Found:
NameDurant
Assignment 1INDEX(A14:H23,MATCH(B1,A14:A23,0),3)
Assignment 2INDEX(A14:H23,MATCH(B1,A14:A23,0),4)
MidtermINDEX(A14:H23,MATCH(B1,A14:A23,0),5)
ProjectINDEX(A14:H23,MATCH(B1,A14:A23,0),6)
ExamINDEX(A14:H23,MATCH(B1,A14:A23,0),7)
GradeINDEX(A14:H23,MATCH(B1,A14:A23,0),8)
StudentID
NameAssignment 1Assignment 2MidtermProjectExamGrade
252549Chad687377938582.10%
318312Durant717466816670.30%
295160Elly315647474244.30%
297061Jordan426550607161.10%
258600Lea487073696064.20%
287519Rolex586243556256.40%
327862Shawlty746257716766.00%
326873Steven534245684750.90%
334166Ali707581837778.10%
331347Twanay385162425350.90%

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

alansidman

Well-known Member
Joined
Feb 26, 2007
Messages
6,191
Office Version
  1. 365
Platform
  1. Windows
vCDEFGHIJ
7StudentID:258600
8
9Student Information Found:
10NameLea=VLOOKUP($D$7,$C$20:$J$29,2,0)
11Assignment 148=VLOOKUP($D$7,$C$20:$J$29,3,0)
12Assignment 270=VLOOKUP($D$7,$C$20:$J$29,4,0)
13Midterm73=VLOOKUP($D$7,$C$20:$J$29,5,0)
14Project69=VLOOKUP($D$7,$C$20:$J$29,6,0)
15Exam60=VLOOKUP($D$7,$C$20:$J$29,7,0)
16Grade64.20%=VLOOKUP($D$7,$C$20:$J$29,8,0)
17
18
19StudentIDNameAssignment 1Assignment 2MidtermProjectExamGrade
20252549Chad687377938582.10%
21318312Durant717466816670.30%
22295160Elly315647474244.30%
23297061Jordan426550607161.10%
24258600Lea487073696064.20%
25287519Rolex586243556256.40%
26327862Shawlty746257716766.00%
27326873Steven534245684750.90%
28334166Ali707581837778.10%
29331347Twanay385162425350.90%
 

jakebb

New Member
Joined
Dec 2, 2016
Messages
2
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
Joined
Feb 26, 2007
Messages
6,191
Office Version
  1. 365
Platform
  1. Windows
the simple answer is no as this determines which column to get the data from.
 

Tetra201

MrExcel MVP
Joined
Oct 14, 2016
Messages
3,643
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)
 

Watch MrExcel Video

Forum statistics

Threads
1,130,119
Messages
5,640,218
Members
417,131
Latest member
Seanr19871

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top