Formula That Matches 2 Criteria & Gives value in another coloumn

[Jamidd1]

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

 

[oldbrewer]

Student Name	Course Code	Qualification	DOE	GRADE
John	MF485	BTEC Home Cooking Skills L2 Award GCSEQL	Apr-18	P
John	MF484	BTEC Home Cooking Skills L1 Award GCSEQL	Jun-18	P
John	5930	AQA Mathematics Entry Level	Jun-18	EL3
John	5972	Silver Step Up to English	Jun-18	X
John	BBTJ7R	Edexcel English Entry Level 1/2/3 Reading
John	BBTJ7W	Edexcel English Entry Level 1/2/3 Writing
John	BBTJ7S	Edexcel English Entry Level 1/2/3 Speaking
Sam	5930	AQA Mathematics Entry Level	Jun-18	EL3
Sam	5972	Silver Step Up to English	Jun-18	X
Sam	BBTJ7R	Edexcel English Entry Level 1/2/3 Reading
Sam	BBTJ7W	Edexcel English Entry Level 1/2/3 Writing
Sam	BBTJ7S	Edexcel English Entry Level 1/2/3 Speaking
Sam	5930	AQA Mathematics Entry Level	Jun-18	X
		Student Name	Sam
		Course Code	5930
		Count of GRADE	GRADE
			EL3	X	Grand Total
		Total	1	1	2
		a simple pivot table finds EL3 and x
		easy to tidy up and format…………

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

 

[Special-K99]

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

 

[AlanY]

try this in G6, and copy down

Book1
	A	B	C	D	E	F	G	H
1	Student Name	Course Code	Qualification	DOE	GRADE
2	John	MF485	BTEC Home Cooking Skills L2 Award GCSEQL	Apr-18	P		Match Criteria 1	Sam
3	John	MF484	BTEC Home Cooking Skills L1 Award GCSEQL	Jun-18	P		Match Criteria 2	5930
4	John	5930	AQA Mathematics Entry Level	Jun-18	EL3
5	John	5972	Silver Step Up to English	Jun-18	X		Result Desired
6	John	BBTJ7R	Edexcel English Entry Level 1/2/3 Reading				EL3
7	John	BBTJ7W	Edexcel English Entry Level 1/2/3 Writing				X
8	John	BBTJ7S	Edexcel English Entry Level 1/2/3 Speaking
9	Sam	5930	AQA Mathematics Entry Level	Jun-18	EL3
10	Sam	5972	Silver Step Up to English	Jun-18	X
11	Sam	BBTJ7R	Edexcel English Entry Level 1/2/3 Reading
12	Sam	BBTJ7W	Edexcel English Entry Level 1/2/3 Writing
13	Sam	BBTJ7S	Edexcel English Entry Level 1/2/3 Speaking
14	Sam	5930	AQA Mathematics Entry Level	Jun-18	X
Exam Input

Cell Formulas
Range	Formula
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.

 

[Jamidd1]

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!

Student Name	Course Code	Qualification	DOE	GRADE
John	MF485	BTEC Home Cooking Skills L2 Award GCSEQL	Apr-18	P
John	MF484	BTEC Home Cooking Skills L1 Award GCSEQL	Jun-18	P
John	5930	AQA Mathematics Entry Level	Jun-18	EL3
John	5972	Silver Step Up to English	Jun-18	X
John	BBTJ7R	Edexcel English Entry Level 1/2/3 Reading
John	BBTJ7W	Edexcel English Entry Level 1/2/3 Writing
John	BBTJ7S	Edexcel English Entry Level 1/2/3 Speaking
Sam	5930	AQA Mathematics Entry Level	Jun-18	EL3
Sam	5972	Silver Step Up to English	Jun-18	X
Sam	BBTJ7R	Edexcel English Entry Level 1/2/3 Reading
Sam	BBTJ7W	Edexcel English Entry Level 1/2/3 Writing
Sam	BBTJ7S	Edexcel English Entry Level 1/2/3 Speaking
Sam	5930	AQA Mathematics Entry Level	Jun-18	X
		Student Name	Sam
		Course Code	5930
		Count of GRADE	GRADE
			EL3	X	Grand Total
		Total	1	1	2
		a simple pivot table finds EL3 and x
		easy to tidy up and format…………

<tbody>
</tbody>