Index Match Query

[kankana]

Hi Folks,

Can you please help me with the below query:

Master Data:

[TABLE="width: 198"]
<colgroup><col><col span="2"></colgroup><tbody>[TR]
[TD]Username[/TD]
[TD]Subject[/TD]
[TD]Score[/TD]
[/TR]
[TR]
[TD]A1[/TD]
[TD]English[/TD]
[TD="align: right"]50[/TD]
[/TR]
[TR]
[TD]A1[/TD]
[TD]Maths[/TD]
[TD="align: right"]45[/TD]
[/TR]
[TR]
[TD]A1[/TD]
[TD]Physics[/TD]
[TD="align: right"]34[/TD]
[/TR]
[TR]
[TD]A1[/TD]
[TD]History[/TD]
[TD="align: right"]23[/TD]
[/TR]
[TR]
[TD]A1[/TD]
[TD]Geography[/TD]
[TD="align: right"]56[/TD]
[/TR]
[TR]
[TD]A2[/TD]
[TD]English[/TD]
[TD="align: right"]24[/TD]
[/TR]
[TR]
[TD]A2[/TD]
[TD]Maths[/TD]
[TD="align: right"]67[/TD]
[/TR]
[TR]
[TD]A2[/TD]
[TD]Physics[/TD]
[TD="align: right"]45[/TD]
[/TR]
[TR]
[TD]A2[/TD]
[TD]History[/TD]
[TD="align: right"]32[/TD]
[/TR]
[TR]
[TD]A3[/TD]
[TD]English[/TD]
[TD="align: right"]67[/TD]
[/TR]
[TR]
[TD]A3[/TD]
[TD]Maths[/TD]
[TD="align: right"]89[/TD]
[/TR]
[TR]
[TD]A3[/TD]
[TD]Physics[/TD]
[TD="align: right"]65[/TD]
[/TR]
[TR]
[TD]A3[/TD]
[TD]History[/TD]
[TD="align: right"]75[/TD]
[/TR]
[TR]
[TD]A3[/TD]
[TD]Geography[/TD]
[TD="align: right"]72[/TD]
[/TR]
[TR]
[TD]A4[/TD]
[TD]Maths[/TD]
[TD="align: right"]45[/TD]
[/TR]
[TR]
[TD]A4[/TD]
[TD]Physics[/TD]
[TD="align: right"]25[/TD]
[/TR]
[TR]
[TD]A4[/TD]
[TD]History[/TD]
[TD="align: right"]67[/TD]
[/TR]
[TR]
[TD]A4[/TD]
[TD]Geography[/TD]
[TD="align: right"]45[/TD]
[/TR]
</tbody>[/TABLE]

Result Data:

[TABLE="width: 380"]
<colgroup><col><col><col><col><col><col></colgroup><tbody>[TR]
[TD]Username[/TD]
[TD]History[/TD]
[TD]Physics[/TD]
[TD]Geography[/TD]
[TD]Maths[/TD]
[TD]English[/TD]
[/TR]
[TR]
[TD]A1[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]A2[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]A3[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]A4[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
</tbody>[/TABLE]

Please help

 

[wideboydixon]

A couple of possibilities:

Book1
	A	B	C	D	E	F	G	H	I	J
1	Username	Subject	Score		Username	History	Physics	Geography	Maths	English
2	A1	English	50		A1	23	34	56	45	50
3	A1	Maths	45		A2	32	45	0	67	24
4	A1	Physics	34		A3	75	65	72	89	67
5	A1	History	23		A4	67	25	45	45	0
6	A1	Geography	56
7	A2	English	24		Username	History	Physics	Geography	Maths	English
8	A2	Maths	67		A1	23	34	56	45	50
9	A2	Physics	45		A2	32	45		67	24
10	A2	History	32		A3	75	65	72	89	67
11	A3	English	67		A4	67	25	45	45
12	A3	Maths	89
13	A3	Physics	65
14	A3	History	75
15	A3	Geography	72
16	A4	Maths	45
17	A4	Physics	25
18	A4	History	67
19	A4	Geography	45
Sheet1

Cell Formulas
Range	Formula
F2	=SUMPRODUCT(($A$2:$A$19=$E2)*($B$2:$B$19=F$1),$C$2:$C$19)
F8	{=IFERROR(VLOOKUP($E8&F$7,CHOOSE({1,2},$A$2:$A$19&$B$2:$B$19,$C$2:$C$19),2,0),"")}
Press CTRL+SHIFT+ENTER to enter array formulas.

Note the second one will show blank if there is no record but the first one will show zero. Of course, you can also format the cell to show zero as blank.

WBD

 

[kankana]

Thanks so much for the help. I have tried the first one and its working for one cell and when i am dragging the formula its not returning the actual result. can you please suggest?

A couple of possibilities:

	A	B	C	D	E	F	G	H	I	J
Username	Subject	Score	Username	History	Physics	Geography	Maths	English
A1	English	A1
A1	Maths	A2
A1	Physics	A3
A1	History	A4
A1	Geography
A2	English	Username	History	Physics	Geography	Maths	English
A2	Maths	A1
A2	Physics	A2
A2	History	A3
A3	English	A4
A3	Maths
A3	Physics
A3	History
A3	Geography
A4	Maths
A4	Physics
A4	History
A4	Geography

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]

[TD="align: right"][/TD]

[TD="align: center"]2[/TD]

[TD="align: right"]50[/TD]
[TD="align: right"][/TD]

[TD="align: right"]23[/TD]
[TD="align: right"]34[/TD]
[TD="align: right"]56[/TD]
[TD="align: right"]45[/TD]
[TD="align: right"]50[/TD]

[TD="align: center"]3[/TD]

[TD="align: right"]45[/TD]
[TD="align: right"][/TD]

[TD="align: right"]32[/TD]
[TD="align: right"]45[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]67[/TD]
[TD="align: right"]24[/TD]

[TD="align: center"]4[/TD]

[TD="align: right"]34[/TD]
[TD="align: right"][/TD]

[TD="align: right"]75[/TD]
[TD="align: right"]65[/TD]
[TD="align: right"]72[/TD]
[TD="align: right"]89[/TD]
[TD="align: right"]67[/TD]

[TD="align: center"]5[/TD]

[TD="align: right"]23[/TD]
[TD="align: right"][/TD]

[TD="align: right"]67[/TD]
[TD="align: right"]25[/TD]
[TD="align: right"]45[/TD]
[TD="align: right"]45[/TD]
[TD="align: right"]0[/TD]

[TD="align: center"]6[/TD]

[TD="align: right"]56[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]7[/TD]

[TD="align: right"]24[/TD]
[TD="align: right"][/TD]

[TD="align: center"]8[/TD]

[TD="align: right"]67[/TD]
[TD="align: right"][/TD]

[TD="align: right"]23[/TD]
[TD="align: right"]34[/TD]
[TD="align: right"]56[/TD]
[TD="align: right"]45[/TD]
[TD="align: right"]50[/TD]

[TD="align: center"]9[/TD]

[TD="align: right"]45[/TD]
[TD="align: right"][/TD]

[TD="align: right"]32[/TD]
[TD="align: right"]45[/TD]

[TD="align: right"]67[/TD]
[TD="align: right"]24[/TD]

[TD="align: center"]10[/TD]

[TD="align: right"]32[/TD]
[TD="align: right"][/TD]

[TD="align: right"]75[/TD]
[TD="align: right"]65[/TD]
[TD="align: right"]72[/TD]
[TD="align: right"]89[/TD]
[TD="align: right"]67[/TD]

[TD="align: center"]11[/TD]

[TD="align: right"]67[/TD]
[TD="align: right"][/TD]

[TD="align: right"]67[/TD]
[TD="align: right"]25[/TD]
[TD="align: right"]45[/TD]
[TD="align: right"]45[/TD]

[TD="align: center"]12[/TD]

[TD="align: right"]89[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]13[/TD]

[TD="align: right"]65[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]14[/TD]

[TD="align: right"]75[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]15[/TD]

[TD="align: right"]72[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]16[/TD]

[TD="align: right"]45[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]17[/TD]

[TD="align: right"]25[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]18[/TD]

[TD="align: right"]67[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]19[/TD]

[TD="align: right"]45[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>

Sheet1​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]F2[/TH]
[TD="align: left"]=SUMPRODUCT(($A$2:$A$19=$E2)*($B$2:$B$19=F$1),$C$2:$C$19)[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Array Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]F8[/TH]
[TD="align: left"]{=IFERROR(VLOOKUP($E8&F$7,CHOOSE({1,2},$A$2:$A$19&$B$2:$B$19,$C$2:$C$19),2,0),"")}[/TD]
[/TR]
</tbody>[/TABLE]
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself[/TD]
[/TR]
</tbody>[/TABLE]



Note the second one will show blank if there is no record but the first one will show zero. Of course, you can also format the cell to show zero as blank.

WBD

 

[wideboydixon]

You have to have careful use of the "$" anchors. Make sure that you have something like this:

=SUMPRODUCT(($A$2:$A$19=$E2)*($B$2:$B$19=F$1),$C$2:$C$19)

$A$2:$A$19 is the range of usernames
$E2 is the cell that contains the usernames. The $E part ensures that the column doesn't change when you paste the formula across
$B$2:$B$19 is the range of subjects
F$1 is the cell that contains the subject. The $1 part ensures that the row doesn't change when you paste the formula down
$C$2:$C$19 is the range of scores

In my example I copied the formula across and down without issue and the values returned as expected.

WBD