index / match with multiple criteria

[Manexcel]

I have a rather large dataset (sheet1) that contains names, year and grade

Example key dataset fields

Name Year Grade
Fred bloggs 2000 A1
Mary Smith 2016 A2
john Jones 2017 A1
John Bloggs 2016 A3
Ros bloggs 2000 A3

I can filter / copy the data onto sheet 2 on any of the criteria

But I would prefer to create the data via the index/match formula on sheet 2, for example those names, year and grade for 2016 only.


Output

Mary Smith A2
John Bloggs A3

etc.


I have been trying to resolve but without success. Can you please assist?

I thank you in anticipation.

 

[Scott T]

Excel 2010[TABLE="class: grid, width: 300"]
<tbody>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH][/TH]
[TH]A
[/TH]
[TH]B
[/TH]
[TH]C
[/TH]
[/TR]
[TR]
[TD="align: center"]1
[/TD]
[TD]Name
[/TD]
[TD]Year
[/TD]
[TD]Grade
[/TD]
[/TR]
[TR]
[TD="align: center"]2
[/TD]
[TD]Fred bloggs
[/TD]
[TD="align: right"]2000
[/TD]
[TD]A1
[/TD]
[/TR]
[TR]
[TD="align: center"]3
[/TD]
[TD]Mary Smith
[/TD]
[TD="align: right"]2016
[/TD]
[TD]A2
[/TD]
[/TR]
[TR]
[TD="align: center"]4
[/TD]
[TD]john Jones
[/TD]
[TD="align: right"]2017
[/TD]
[TD]A1
[/TD]
[/TR]
[TR]
[TD="align: center"]5
[/TD]
[TD]John Bloggs
[/TD]
[TD="align: right"]2016
[/TD]
[TD]A3
[/TD]
[/TR]
[TR]
[TD="align: center"]6
[/TD]
[TD]Ros bloggs
[/TD]
[TD="align: right"]2000
[/TD]
[TD]A3
[/TD]
[/TR]
</tbody>[/TABLE]

Sheet1​

Excel 2010[TABLE="class: grid, width: 300"]
<colgroup><col><col><col><col><col><col></colgroup><tbody>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[/TR]
[TR]
[TD="align: center"]1[/TD]
[TD]Name[/TD]
[TD]Year[/TD]
[TD]Grade[/TD]
[TD="align: right"][/TD]
[TD]year[/TD]
[/TR]
[TR]
[TD="align: center"]2[/TD]
[TD]Mary Smith[/TD]
[TD="align: right"]2016[/TD]
[TD]A2[/TD]
[TD="align: right"][/TD]
[TD="align: right"]2016[/TD]
[/TR]
[TR]
[TD="align: center"]3[/TD]
[TD]John Bloggs[/TD]
[TD="align: right"]2016[/TD]
[TD]A3[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: center"]4[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: center"]5[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
</tbody>[/TABLE]

Sheet2​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Array Formulas[TABLE="class: grid, width: 100%"]
<tbody>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TD="width: 10"]Cell[/TD]
[TD="align: left"]Formula[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]A2[/TH]
[TD="align: left"]{=IF(ROWS($A$2:A2)>COUNTIF(Sheet1!$B$2:$B$6,$E$2),"",INDEX(Sheet1!A$2:A$6,SMALL(IF($E$2=Sheet1!$B$2:$B$6,ROW(Sheet1!$B$2:$B$6)-ROW(Sheet1!$B$2)+1),ROWS($A$2:A2))))}[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]B2[/TH]
[TD="align: left"]{=IF(ROWS($A$2:B2)>COUNTIF(Sheet1!$B$2:$B$6,$E$2),"",INDEX(Sheet1!B$2:B$6,SMALL(IF($E$2=Sheet1!$B$2:$B$6,ROW(Sheet1!$B$2:$B$6)-ROW(Sheet1!$B$2)+1),ROWS($A$2:B2))))}[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]C2[/TH]
[TD="align: left"]{=IF(ROWS($A$2:C2)>COUNTIF(Sheet1!$B$2:$B$6,$E$2),"",INDEX(Sheet1!C$2:C$6,SMALL(IF($E$2=Sheet1!$B$2:$B$6,ROW(Sheet1!$B$2:$B$6)-ROW(Sheet1!$B$2)+1),ROWS($A$2:C2))))}[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]A3[/TH]
[TD="align: left"]{=IF(ROWS($A$2:A3)>COUNTIF(Sheet1!$B$2:$B$6,$E$2),"",INDEX(Sheet1!A$2:A$6,SMALL(IF($E$2=Sheet1!$B$2:$B$6,ROW(Sheet1!$B$2:$B$6)-ROW(Sheet1!$B$2)+1),ROWS($A$2:A3))))}[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]B3[/TH]
[TD="align: left"]{=IF(ROWS($A$2:B3)>COUNTIF(Sheet1!$B$2:$B$6,$E$2),"",INDEX(Sheet1!B$2:B$6,SMALL(IF($E$2=Sheet1!$B$2:$B$6,ROW(Sheet1!$B$2:$B$6)-ROW(Sheet1!$B$2)+1),ROWS($A$2:B3))))}[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]C3[/TH]
[TD="align: left"]{=IF(ROWS($A$2:C3)>COUNTIF(Sheet1!$B$2:$B$6,$E$2),"",INDEX(Sheet1!C$2:C$6,SMALL(IF($E$2=Sheet1!$B$2:$B$6,ROW(Sheet1!$B$2:$B$6)-ROW(Sheet1!$B$2)+1),ROWS($A$2:C3))))}[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]A4[/TH]
[TD="align: left"]{=IF(ROWS($A$2:A4)>COUNTIF(Sheet1!$B$2:$B$6,$E$2),"",INDEX(Sheet1!A$2:A$6,SMALL(IF($E$2=Sheet1!$B$2:$B$6,ROW(Sheet1!$B$2:$B$6)-ROW(Sheet1!$B$2)+1),ROWS($A$2:A4))))}[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]B4[/TH]
[TD="align: left"]{=IF(ROWS($A$2:B4)>COUNTIF(Sheet1!$B$2:$B$6,$E$2),"",INDEX(Sheet1!B$2:B$6,SMALL(IF($E$2=Sheet1!$B$2:$B$6,ROW(Sheet1!$B$2:$B$6)-ROW(Sheet1!$B$2)+1),ROWS($A$2:B4))))}[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]C4[/TH]
[TD="align: left"]{=IF(ROWS($A$2:C4)>COUNTIF(Sheet1!$B$2:$B$6,$E$2),"",INDEX(Sheet1!C$2:C$6,SMALL(IF($E$2=Sheet1!$B$2:$B$6,ROW(Sheet1!$B$2:$B$6)-ROW(Sheet1!$B$2)+1),ROWS($A$2:C4))))}[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]A5[/TH]
[TD="align: left"]{=IF(ROWS($A$2:A5)>COUNTIF(Sheet1!$B$2:$B$6,$E$2),"",INDEX(Sheet1!A$2:A$6,SMALL(IF($E$2=Sheet1!$B$2:$B$6,ROW(Sheet1!$B$2:$B$6)-ROW(Sheet1!$B$2)+1),ROWS($A$2:A5))))}[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]B5[/TH]
[TD="align: left"]{=IF(ROWS($A$2:B5)>COUNTIF(Sheet1!$B$2:$B$6,$E$2),"",INDEX(Sheet1!B$2:B$6,SMALL(IF($E$2=Sheet1!$B$2:$B$6,ROW(Sheet1!$B$2:$B$6)-ROW(Sheet1!$B$2)+1),ROWS($A$2:B5))))}[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]C5[/TH]
[TD="align: left"]{=IF(ROWS($A$2:C5)>COUNTIF(Sheet1!$B$2:$B$6,$E$2),"",INDEX(Sheet1!C$2:C$6,SMALL(IF($E$2=Sheet1!$B$2:$B$6,ROW(Sheet1!$B$2:$B$6)-ROW(Sheet1!$B$2)+1),ROWS($A$2:C5))))}[/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]

 

[Manexcel]

Thank you so much for the quick response and resolution. All is working ok. Great job.

If I may ask another question...

I have expanded the formulas across and down each column beyond the original 3 columns and 6 rows and no issues.

I have also changed the formulas to reflect different criteria I may require going forward from my original dataset. Again no issues.

However, If I wanted to be selective on the columns that I require on sheet2 is that possible. e.g.

Only Columns A and D and L and M and P and Z?


Thank you for your patience and understanding

 

[Scott T]

If the headers are the same in each sheet then this should work. Since this looks for a match between the headers in each sheet they must be the same If one has an extra space then no match will be found. "Grade" in one sheet and "Grade " in the other sheet will cause an N/A error since the match could not be found.

Excel 2010

	A	B	C	D	E
Name	Grade	year
Mary Smith	A2
John Bloggs	A3

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

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

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

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

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

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

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

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

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

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

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

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

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

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

</tbody>

Sheet2​

[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: 10"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]A2[/TH]
[TD="align: left"]{=IF(ROWS($A$2:A2)>COUNTIF(Sheet1!$B$2:$B$6,$E$2),"",INDEX(Sheet1!$A$2:C$6,SMALL(IF($E$2=Sheet1!$B$2:$B$6,ROW(Sheet1!$B$2:$B$6)-ROW(Sheet1!$B$2)+1),ROWS($A$2:A2)),MATCH(A$1,Sheet1!$A$1:$C$1,0)))}[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]B2[/TH]
[TD="align: left"]{=IF(ROWS($A$2:B2)>COUNTIF(Sheet1!$B$2:$B$6,$E$2),"",INDEX(Sheet1!$A$2:D$6,SMALL(IF($E$2=Sheet1!$B$2:$B$6,ROW(Sheet1!$B$2:$B$6)-ROW(Sheet1!$B$2)+1),ROWS($A$2:B2)),MATCH(B$1,Sheet1!$A$1:$C$1,0)))}[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]A3[/TH]
[TD="align: left"]{=IF(ROWS($A$2:A3)>COUNTIF(Sheet1!$B$2:$B$6,$E$2),"",INDEX(Sheet1!$A$2:C$6,SMALL(IF($E$2=Sheet1!$B$2:$B$6,ROW(Sheet1!$B$2:$B$6)-ROW(Sheet1!$B$2)+1),ROWS($A$2:A3)),MATCH(A$1,Sheet1!$A$1:$C$1,0)))}[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]B3[/TH]
[TD="align: left"]{=IF(ROWS($A$2:B3)>COUNTIF(Sheet1!$B$2:$B$6,$E$2),"",INDEX(Sheet1!$A$2:D$6,SMALL(IF($E$2=Sheet1!$B$2:$B$6,ROW(Sheet1!$B$2:$B$6)-ROW(Sheet1!$B$2)+1),ROWS($A$2:B3)),MATCH(B$1,Sheet1!$A$1:$C$1,0)))}[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]A4[/TH]
[TD="align: left"]{=IF(ROWS($A$2:A4)>COUNTIF(Sheet1!$B$2:$B$6,$E$2),"",INDEX(Sheet1!$A$2:C$6,SMALL(IF($E$2=Sheet1!$B$2:$B$6,ROW(Sheet1!$B$2:$B$6)-ROW(Sheet1!$B$2)+1),ROWS($A$2:A4)),MATCH(A$1,Sheet1!$A$1:$C$1,0)))}[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]B4[/TH]
[TD="align: left"]{=IF(ROWS($A$2:B4)>COUNTIF(Sheet1!$B$2:$B$6,$E$2),"",INDEX(Sheet1!$A$2:D$6,SMALL(IF($E$2=Sheet1!$B$2:$B$6,ROW(Sheet1!$B$2:$B$6)-ROW(Sheet1!$B$2)+1),ROWS($A$2:B4)),MATCH(B$1,Sheet1!$A$1:$C$1,0)))}[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]A5[/TH]
[TD="align: left"]{=IF(ROWS($A$2:A5)>COUNTIF(Sheet1!$B$2:$B$6,$E$2),"",INDEX(Sheet1!$A$2:C$6,SMALL(IF($E$2=Sheet1!$B$2:$B$6,ROW(Sheet1!$B$2:$B$6)-ROW(Sheet1!$B$2)+1),ROWS($A$2:A5)),MATCH(A$1,Sheet1!$A$1:$C$1,0)))}[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]B5[/TH]
[TD="align: left"]{=IF(ROWS($A$2:B5)>COUNTIF(Sheet1!$B$2:$B$6,$E$2),"",INDEX(Sheet1!$A$2:D$6,SMALL(IF($E$2=Sheet1!$B$2:$B$6,ROW(Sheet1!$B$2:$B$6)-ROW(Sheet1!$B$2)+1),ROWS($A$2:B5)),MATCH(B$1,Sheet1!$A$1:$C$1,0)))}[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]A6[/TH]
[TD="align: left"]{=IF(ROWS($A$2:A6)>COUNTIF(Sheet1!$B$2:$B$6,$E$2),"",INDEX(Sheet1!A$2:A$6,SMALL(IF($E$2=Sheet1!$B$2:$B$6,ROW(Sheet1!$B$2:$B$6)-ROW(Sheet1!$B$2)+1),ROWS($A$2:A6))))}[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]B6[/TH]
[TD="align: left"]{=IF(ROWS($A$2:B6)>COUNTIF(Sheet1!$B$2:$B$6,$E$2),"",INDEX(Sheet1!B$2:B$6,SMALL(IF($E$2=Sheet1!$B$2:$B$6,ROW(Sheet1!$B$2:$B$6)-ROW(Sheet1!$B$2)+1),ROWS($A$2:B6))))}[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]A7[/TH]
[TD="align: left"]{=IF(ROWS($A$2:A7)>COUNTIF(Sheet1!$B$2:$B$6,$E$2),"",INDEX(Sheet1!A$2:A$6,SMALL(IF($E$2=Sheet1!$B$2:$B$6,ROW(Sheet1!$B$2:$B$6)-ROW(Sheet1!$B$2)+1),ROWS($A$2:A7))))}[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]B7[/TH]
[TD="align: left"]{=IF(ROWS($A$2:B7)>COUNTIF(Sheet1!$B$2:$B$6,$E$2),"",INDEX(Sheet1!B$2:B$6,SMALL(IF($E$2=Sheet1!$B$2:$B$6,ROW(Sheet1!$B$2:$B$6)-ROW(Sheet1!$B$2)+1),ROWS($A$2:B7))))}[/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]

 

[Manexcel]

Scott T thank you so much. It worked perfectly first time. Great job. Very pleased.



Scott, I am not sure how the protocols work for adding subscribed threads that have had no responses. So my profound apologies now to all.

I raised another key challenge for me titled; Data manipulation which I have yet had anyone to take up the 'baton'.

Is it a too big a challenge to resolve?

Once again thank you for your assistance greatly appreciated.