using vlookup

[Akki786]

There are 3 students with marks in 3 different subjects for 3 different terms. how can i use vlookup or indexing such that when input student name and subject in respective cells then i get output marks for all three terms. For example i want raghav marks in english for all terms. Please explain in simple steps.

 

[etaf]

which cells are you putting the name and subject in
is the image the table you want to to look up

are you still using excel version 2007

 

[Cubist]

One option is INDEX,MATCH.

Book3
	A	B	C	D	E	F	G	H	I	J	K
1	Student	Subject	Term 1	Term 2	Term 3
2	A	H	61	88	90		Student	Subject	Term 1	Term 2	Term 3
3	A	E	87	100	76		A	H	61	88	90
4	A	M	57	50	52
5	R	H	74	57	55
6	R	E	75	93	72
7	R	M	92	65	60
8	S	H	93	86	69
9	S	E	78	67	96
10	S	M	91	80	70
Sheet3

Cell Formulas
Range	Formula
I3:K3	I3	=INDEX($C$2:$E$10,MATCH($G$3&$H$3,$A$2:$A$10&$B$2:$B$10,0),MATCH(I2,$C$1:$E$1,0))

 

[Akki786]

there are students named Akshat,Raghav and sanchit with marks in 3 subjects named hindi,english and maths for 3 different terms.
How do I apply formula such that I get marks for all 3 terms on selecting a student and the subject .

 

[RobOrBob]

Check out XLOOKUP - this is a good write up:

XLOOKUP Function

The Excel XLOOKUP function is a modern and flexible replacement for older functions like VLOOKUP, HLOOKUP, and LOOKUP. XLOOKUP supports approximate and exact matching, wildcards (* ?) for partial matches, and lookups in vertical or horizontal ranges.

exceljet.net

 

[Cubist]

Hi @RobOrBob , XLOOKUP isn't available in Excel2007.

 

[RobOrBob]

Sorry! Lots of good stuff in 365