Vlookup function

[jakebb]

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>

 

[Anand Sharma]

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]

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]

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]

the simple answer is no as this determines which column to get the data from.

 

[Tetra201]

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)