Formula That Matches 2 Criteria & Gives value in another coloumn

J

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

Excel motto
Not everything I do at work revolves around Excel. Only the fun parts.
Sort by date Sort by votes
[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!

oldbrewer said:
[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]
Click to expand...
 
Upvote 0
You must log in or register to reply here.

Similar threads

C
  • Solved
Lookup multiple criteria and find match within mixed alpha numerical cells and return the value matched
2 3
Replies
21
Views
880
Chrissy_M
C
N
  • Solved
Help with formula please, if two values match calculate the difference between 2 dates
2
Replies
11
Views
435
Newbie73
N
R
  • Solved
Using Countif with Match or something similar?
Replies
5
Views
236
Fluff
Fluff
B
  • Question Question
Find and match partial text between 2 tables, return value
Replies
0
Views
170
BudgetGirl
B
A
  • Question Question
Excel - looking up data with multiple criteria from table/array, date must be same or nearest before the target cell
Replies
8
Views
561
Peter_SSs
Peter_SSs

Forum statistics

Threads
1,223,903
Messages
6,175,287
Members
452,631
Latest member
a_potato

Share this page

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