Formula That Matches 2 Criteria & Gives value in another coloumn

Jamidd1

New Member
Joined
Feb 10, 2016
Messages
14
Hi Mr Excel,

I am trying to work out a formula that matches two criteria & returns the value in an adjacent cell. I can find this quite simply but I was wondering if there was a way to use an Array Formula to return all matches & not just the first or last match.

Please see example with result needed below.

Link to document as I can not upload attachments. - https://drive.google.com/file/d/1-yL_kGrDKAoZBEaVyJgKXHbqI60Qepl_/view?usp=sharing

Let me know if you need any further information.

Thank you in advance.

Jamidd
 

Excel Facts

Copy PDF to Excel
Select data in PDF. Paste to Microsoft Word. Copy from Word and paste to Excel.
[TABLE="width: 909"]
<colgroup><col><col><col><col span="2"><col span="3"></colgroup><tbody>[TR]
[TD]Student Name[/TD]
[TD]Course Code[/TD]
[TD]Qualification[/TD]
[TD]DOE[/TD]
[TD]GRADE[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]John[/TD]
[TD]MF485[/TD]
[TD]BTEC Home Cooking Skills L2 Award GCSEQL[/TD]
[TD]Apr-18[/TD]
[TD]P[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]John[/TD]
[TD]MF484[/TD]
[TD]BTEC Home Cooking Skills L1 Award GCSEQL[/TD]
[TD]Jun-18[/TD]
[TD]P[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]John[/TD]
[TD]5930[/TD]
[TD]AQA Mathematics Entry Level[/TD]
[TD]Jun-18[/TD]
[TD]EL3[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]John[/TD]
[TD]5972[/TD]
[TD]Silver Step Up to English[/TD]
[TD]Jun-18[/TD]
[TD]X[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]John[/TD]
[TD]BBTJ7R[/TD]
[TD]Edexcel English Entry Level 1/2/3 Reading[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]John[/TD]
[TD]BBTJ7W[/TD]
[TD]Edexcel English Entry Level 1/2/3 Writing[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]John[/TD]
[TD]BBTJ7S[/TD]
[TD]Edexcel English Entry Level 1/2/3 Speaking[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Sam[/TD]
[TD]5930[/TD]
[TD]AQA Mathematics Entry Level[/TD]
[TD]Jun-18[/TD]
[TD]EL3[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Sam[/TD]
[TD]5972[/TD]
[TD]Silver Step Up to English[/TD]
[TD]Jun-18[/TD]
[TD]X[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Sam[/TD]
[TD]BBTJ7R[/TD]
[TD]Edexcel English Entry Level 1/2/3 Reading[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Sam[/TD]
[TD]BBTJ7W[/TD]
[TD]Edexcel English Entry Level 1/2/3 Writing[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Sam[/TD]
[TD]BBTJ7S[/TD]
[TD]Edexcel English Entry Level 1/2/3 Speaking[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Sam[/TD]
[TD]5930[/TD]
[TD]AQA Mathematics Entry Level[/TD]
[TD]Jun-18[/TD]
[TD]X[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]Student Name[/TD]
[TD]Sam[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]Course Code[/TD]
[TD]5930[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]Count of GRADE[/TD]
[TD]GRADE[/TD]
[TD] [/TD]
[TD] [/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD] [/TD]
[TD]EL3[/TD]
[TD]X[/TD]
[TD]Grand Total[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]Total[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]2[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]a simple pivot table finds EL3 and x[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]easy to tidy up and format…………[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
 
Upvote 0
If you can use AGGREGATE

in G6
=IFERROR(INDEX(E$2:E$14,AGGREGATE(15,6,ROW(A$2:A$14)/((A$2:A$14=H$2)*($B$2:$B$14=H$3)),ROWS(A$2:A2))-(2-1),COLUMN()-(COLUMN()-1),1),"")
and copy down the column

or if you cant use AGGREGATE

in G6
=IFERROR(INDEX(E$2:E$14,SMALL(IF((A$2:A$14=H$2)*(B$2:B$14=H$3),ROW($A$2:$A$14)-(2-1)),ROWS($1:1))),"")
Array formula, use Ctrl-Shift-Enter
 
Upvote 0
try this in G6, and copy down


Book1
ABCDEFGH
1Student NameCourse CodeQualificationDOEGRADE
2JohnMF485BTEC Home Cooking Skills L2 Award GCSEQLApr-18PMatch Criteria 1Sam
3JohnMF484BTEC Home Cooking Skills L1 Award GCSEQLJun-18PMatch Criteria 25930
4John5930AQA Mathematics Entry LevelJun-18EL3
5John5972Silver Step Up to EnglishJun-18XResult Desired
6JohnBBTJ7REdexcel English Entry Level 1/2/3 ReadingEL3
7JohnBBTJ7WEdexcel English Entry Level 1/2/3 WritingX
8JohnBBTJ7SEdexcel English Entry Level 1/2/3 Speaking
9Sam5930AQA Mathematics Entry LevelJun-18EL3
10Sam5972Silver Step Up to EnglishJun-18X
11SamBBTJ7REdexcel English Entry Level 1/2/3 Reading
12SamBBTJ7WEdexcel English Entry Level 1/2/3 Writing
13SamBBTJ7SEdexcel English Entry Level 1/2/3 Speaking
14Sam5930AQA Mathematics Entry LevelJun-18X
Exam Input
Cell Formulas
RangeFormula
G6{=IF(COUNTIFS($A$2:$A$17,$H$2,$B$2:$B$17,$H$3)>=ROWS($G$6:G6),INDEX($E$2:$E$17,SMALL(IF($A$2:$A$17=$H$2,ROW($A$2:$A$17)-ROW($A$2)+1),ROWS($G$6:G6))),"")}
Press CTRL+SHIFT+ENTER to enter array formulas.
 
Upvote 0
Hi,
A Pivot Table was my first thought but for some reason I can not get the pivot table to show the values , I can only get the pivot table to sum them. Any suggestions?

Thanks for this, great idea!

[TABLE="width: 909"]
<tbody>[TR]
[TD]Student Name
[/TD]
[TD]Course Code
[/TD]
[TD]Qualification
[/TD]
[TD]DOE
[/TD]
[TD]GRADE
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]John
[/TD]
[TD]MF485
[/TD]
[TD]BTEC Home Cooking Skills L2 Award GCSEQL
[/TD]
[TD]Apr-18
[/TD]
[TD]P
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]John
[/TD]
[TD]MF484
[/TD]
[TD]BTEC Home Cooking Skills L1 Award GCSEQL
[/TD]
[TD]Jun-18
[/TD]
[TD]P
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]John
[/TD]
[TD]5930
[/TD]
[TD]AQA Mathematics Entry Level
[/TD]
[TD]Jun-18
[/TD]
[TD]EL3
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]John
[/TD]
[TD]5972
[/TD]
[TD]Silver Step Up to English
[/TD]
[TD]Jun-18
[/TD]
[TD]X
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]John
[/TD]
[TD]BBTJ7R
[/TD]
[TD]Edexcel English Entry Level 1/2/3 Reading
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]John
[/TD]
[TD]BBTJ7W
[/TD]
[TD]Edexcel English Entry Level 1/2/3 Writing
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]John
[/TD]
[TD]BBTJ7S
[/TD]
[TD]Edexcel English Entry Level 1/2/3 Speaking
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Sam
[/TD]
[TD]5930
[/TD]
[TD]AQA Mathematics Entry Level
[/TD]
[TD]Jun-18
[/TD]
[TD]EL3
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Sam
[/TD]
[TD]5972
[/TD]
[TD]Silver Step Up to English
[/TD]
[TD]Jun-18
[/TD]
[TD]X
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Sam
[/TD]
[TD]BBTJ7R
[/TD]
[TD]Edexcel English Entry Level 1/2/3 Reading
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Sam
[/TD]
[TD]BBTJ7W
[/TD]
[TD]Edexcel English Entry Level 1/2/3 Writing
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Sam
[/TD]
[TD]BBTJ7S
[/TD]
[TD]Edexcel English Entry Level 1/2/3 Speaking
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Sam
[/TD]
[TD]5930
[/TD]
[TD]AQA Mathematics Entry Level
[/TD]
[TD]Jun-18
[/TD]
[TD]X
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]Student Name
[/TD]
[TD]Sam
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]Course Code
[/TD]
[TD]5930
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]Count of GRADE
[/TD]
[TD]GRADE
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]EL3
[/TD]
[TD]X
[/TD]
[TD]Grand Total
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]Total
[/TD]
[TD="align: right"]1
[/TD]
[TD="align: right"]1
[/TD]
[TD="align: right"]2
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]a simple pivot table finds EL3 and x
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]easy to tidy up and format…………
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
 
Upvote 0

Forum statistics

Threads
1,223,099
Messages
6,170,107
Members
452,302
Latest member
TaMere

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