Index Match Query

kankana

New Member
Joined
Sep 19, 2017
Messages
3
Hi Folks,

Can you please help me with the below query:

Master Data:

UsernameSubjectScore
A1English50
A1Maths45
A1Physics34
A1History23
A1Geography56
A2English24
A2Maths67
A2Physics45
A2History32
A3English67
A3Maths89
A3Physics65
A3History75
A3Geography72
A4Maths45
A4Physics25
A4History67
A4Geography45

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

Result Data:

UsernameHistoryPhysicsGeographyMathsEnglish
A1
A2
A3
A4

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

Please help
 

Excel Facts

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.
A couple of possibilities:


Book1
ABCDEFGHIJ
1UsernameSubjectScoreUsernameHistoryPhysicsGeographyMathsEnglish
2A1English50A12334564550
3A1Maths45A2324506724
4A1Physics34A37565728967
5A1History23A4672545450
6A1Geography56
7A2English24UsernameHistoryPhysicsGeographyMathsEnglish
8A2Maths67A12334564550
9A2Physics45A232456724
10A2History32A37565728967
11A3English67A467254545
12A3Maths89
13A3Physics65
14A3History75
15A3Geography72
16A4Maths45
17A4Physics25
18A4History67
19A4Geography45
Sheet1
Cell Formulas
RangeFormula
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
 
Upvote 0
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:

ABCDEFGHIJ
1UsernameSubjectScoreUsernameHistoryPhysicsGeographyMathsEnglish
2A1English50A12334564550
3A1Maths45A2324506724
4A1Physics34A37565728967
5A1History23A4672545450
6A1Geography56
7A2English24UsernameHistoryPhysicsGeographyMathsEnglish
8A2Maths67A12334564550
9A2Physics45A232456724
10A2History32A37565728967
11A3English67A467254545
12A3Maths89
13A3Physics65
14A3History75
15A3Geography72
16A4Maths45
17A4Physics25
18A4History67
19A4Geography45

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>
Sheet1

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

<thead>
</thead><tbody>
</tbody>

<tbody>
</tbody>

Array Formulas
CellFormula
F8{=IFERROR(VLOOKUP($E8&F$7,CHOOSE({1,2},$A$2:$A$19&$B$2:$B$19,$C$2:$C$19),2,0),"")}

<thead>
</thead><tbody>
</tbody>
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself

<tbody>
</tbody>



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
 
Upvote 0
You have to have careful use of the "$" anchors. Make sure that you have something like this:

Code:
=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
 
Upvote 0

Forum statistics

Threads
1,213,490
Messages
6,113,957
Members
448,535
Latest member
alrossman

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
Back
Top