Help with array formula

A

alombia

Board Regular
Joined
Jan 14, 2016
Messages
89
ABCDEFGHI…
1NameYearEng 1Eng 2Eng 3Mat1Mat2Mat 3…
2John1342357979034
3Sam1347687341090
4Luke2679845903498
5Lucy3986745678690
6May375476239438

<tbody>
</tbody>

Hi,
I would like help with a formula to retrieve filtered results from the above Sheet called Sheet1 and pasted into Sheet2 in say Cell A1
There are two selection criteria: Year and Subject. I need the formula to display all the results matching the selected criteria. For example, Year 1 and Eng. So Cell A1 has the Year value. Cell A2 has the subject value (subjects are in cell D1:I1)
I’m guessing an array formula would be good so that it can be pasted over into different cells.
 
Sort by date Sort by votes
Hi,

As an initial step, with Data as the first sheet, in cell B2 of your second sheet, you could test :

=IF(ROWS(B$2:B2)<=COUNTIF(Data!$C$2:$C$6,$A$1),INDEX(Data!$B$2:$B$6,SMALL(IF(Data!$C$2:$C$6=$A$1,ROW(Data!$C$2:$C$6)-ROW(Data!$C$2)+1),ROWS(B$2:B2))),"")

HTH
 
Upvote 0
James006 said:
Hi,

As an initial step, with Data as the first sheet, in cell B2 of your second sheet, you could test :

=IF(ROWS(B$2:B2)<=COUNTIF(Data!$C$2:$C$6,$A$1),INDEX(Data!$B$2:$B$6,SMALL(IF(Data!$C$2:$C$6=$A$1,ROW(Data!$C$2:$C$6)-ROW(Data!$C$2)+1),ROWS(B$2:B2))),"")

HTH
Click to expand...

Imasad
Im confused. Your formula does not reference the search criteria. The search values are contained in cell A1 and B1 of sheet2. The formula needs to return results that match both criteria. Here is an example of a returned result:

Criteria in A1= 1 Criteria in B1 = Eng

Results:
[TABLE="class: cms_table"]
<tbody>[TR]
[TD]1[/TD]
[TD]Name[/TD]
[TD]Year[/TD]
[TD]Eng 1[/TD]
[TD]Eng 2[/TD]
[TD]Eng 3[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]John[/TD]
[TD]1[/TD]
[TD]34[/TD]
[TD]23[/TD]
[TD]57[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]Sam[/TD]
[TD]1[/TD]
[TD]34[/TD]
[TD]76[/TD]
[TD]87[/TD]
[/TR]
</tbody>[/TABLE]
Criteria in A1 = 3 Criteria in B1= Mat
Results:

[TABLE="class: cms_table"]
<tbody>[TR]
[TD]1

2[/TD]
[TD]Name

Lucy[/TD]
[TD]Year

3[/TD]
[TD]Mat1

67[/TD]
[TD]Mat2

86[/TD]
[TD]Mat3

90[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]May[/TD]
[TD]3[/TD]
[TD]23[/TD]
[TD]94[/TD]
[TD]34[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
 
Upvote 0
SUPER ugly and probably not very robust formulas here...


Excel 2010
ABCDEFGH
1NameYearEng 1Eng 2Eng 3Mat1Mat2Mat3
2John1342357979034
3Sam1347687341090
4Luke2679845903498
5Lucy3986745678690
6May375476239438
Sheet1



Excel 2010
ABCDE
1YearSubject
21Eng
3
4NameYearEng 1Eng 2Eng 3
5John1342357
6Sam1347687
Sheet2
Cell Formulas
RangeFormula
B5=IFERROR(INDEX(Sheet1!$B$2:$B$6,MATCH(A5,Sheet1!$A$2:$A$6,0)),"")
C5=IF(SUMPRODUCT(($A5=Sheet1!$A$2:$A$6)*(C$4=Sheet1!$C$1:$H$1)*(Sheet1!$C$2:$H$6))=0,"",SUMPRODUCT(($A5=Sheet1!$A$2:$A$6)*(C$4=Sheet1!$C$1:$H$1)*(Sheet1!$C$2:$H$6)))
C4{=IFERROR(INDEX(Sheet1!$C1:$H1,SMALL(IF(LEFT(Sheet1!$C1:$H1,3)=$B$2,COLUMN(Sheet1!$C1:$H1)),COLUMN(A$1:A$6))-2),"")}
A5{=IFERROR(INDEX(Sheet1!$A$2:$A$6,SMALL(IF(Sheet1!$B$2:$B$6=$A$2,ROW(Sheet1!$B$2:$B$6)-1),ROW(Sheet1!1:1))),"")}
Press CTRL+SHIFT+ENTER to enter array formulas.




Formulas in column A and B are dragged down. Formula in C4 is dragged to the right and the formula in C5 is dragged down and to the right. Hopefully you can adapt this to your needs.
 
Last edited:
Upvote 0
alombia said:
Imasad
Im confused. Your formula does not reference the search criteria. The search values are contained in cell A1 and B1 of sheet2. The formula needs to return results that match both criteria. Here is an example of a returned result:

Criteria in A1= 1 Criteria in B1 = Eng

Results:
[TABLE="class: cms_table"]
<tbody>[TR]
[TD]1[/TD]
[TD]Name[/TD]
[TD]Year[/TD]
[TD]Eng 1[/TD]
[TD]Eng 2[/TD]
[TD]Eng 3[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]John[/TD]
[TD]1[/TD]
[TD]34[/TD]
[TD]23[/TD]
[TD]57[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]Sam[/TD]
[TD]1[/TD]
[TD]34[/TD]
[TD]76[/TD]
[TD]87[/TD]
[/TR]
</tbody>[/TABLE]
Criteria in A1 = 3 Criteria in B1= Mat
Results:

[TABLE="class: cms_table"]
<tbody>[TR]
[TD]1

2[/TD]
[TD]Name

Lucy[/TD]
[TD]Year

3[/TD]
[TD]Mat1

67[/TD]
[TD]Mat2

86[/TD]
[TD]Mat3

90[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]May[/TD]
[TD]3[/TD]
[TD]23[/TD]
[TD]94[/TD]
[TD]34[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
Click to expand...


I am confused by the fact that you are confused ... :wink:

Test the array formula in Sheet 2 ... and you will see it refers to search criteria ... and generates your list of Unique individuals ...

HTH
 
Upvote 0
Hi,
Yes, i should not have been confused, as it works fine. Many thanks
 
Last edited:
Upvote 0
63falcondude said:
SUPER ugly and probably not very robust formulas here...

Excel 2010
ABCDEFGH
NameYearEng 1Eng 2Eng 3Mat1Mat2Mat3
John
Sam
Luke
Lucy
May

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

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

[TD="align: right"]1[/TD]
[TD="align: right"]34[/TD]
[TD="align: right"]23[/TD]
[TD="align: right"]57[/TD]
[TD="align: right"]97[/TD]
[TD="align: right"]90[/TD]
[TD="align: right"]34[/TD]

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

[TD="align: right"]1[/TD]
[TD="align: right"]34[/TD]
[TD="align: right"]76[/TD]
[TD="align: right"]87[/TD]
[TD="align: right"]34[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]90[/TD]

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

[TD="align: right"]2[/TD]
[TD="align: right"]67[/TD]
[TD="align: right"]98[/TD]
[TD="align: right"]45[/TD]
[TD="align: right"]90[/TD]
[TD="align: right"]34[/TD]
[TD="align: right"]98[/TD]

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

[TD="align: right"]3[/TD]
[TD="align: right"]98[/TD]
[TD="align: right"]67[/TD]
[TD="align: right"]45[/TD]
[TD="align: right"]67[/TD]
[TD="align: right"]86[/TD]
[TD="align: right"]90[/TD]

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

[TD="align: right"]3[/TD]
[TD="align: right"]7[/TD]
[TD="align: right"]54[/TD]
[TD="align: right"]76[/TD]
[TD="align: right"]23[/TD]
[TD="align: right"]94[/TD]
[TD="align: right"]38[/TD]

</tbody>
Sheet1​



Excel 2010
ABCDE
NameYearEng 1Eng 2Eng 3
John
Sam

<tbody>
[TD="align: center"]1[/TD]
[TD="align: center"]Year[/TD]
[TD="align: center"]Subject[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]2[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]Eng[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

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

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

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

[TD="align: right"]1[/TD]
[TD="align: right"]34[/TD]
[TD="align: right"]23[/TD]
[TD="align: right"]57[/TD]

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

[TD="align: right"]1[/TD]
[TD="align: right"]34[/TD]
[TD="align: right"]76[/TD]
[TD="align: right"]87[/TD]

</tbody>
Sheet2​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<tbody>[TR="bgcolor: #DAE7F5"]
[TH]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
[TR]
[TH="bgcolor: #DAE7F5"]B5[/TH]
[TD="align: left"]=IFERROR(INDEX(Sheet1!$B$2:$B$6,MATCH(A5,Sheet1!$A$2:$A$6,0)),"")[/TD]
[/TR]
[TR]
[TH="bgcolor: #DAE7F5"]C5[/TH]
[TD="align: left"]=IF(SUMPRODUCT(($A5=Sheet1!$A$2:$A$6)*(C$4=Sheet1!$C$1:$H$1)*(Sheet1!$C$2:$H$6))=0,"",SUMPRODUCT(($A5=Sheet1!$A$2:$A$6)*(C$4=Sheet1!$C$1:$H$1)*(Sheet1!$C$2:$H$6)))[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Array Formulas[TABLE="width: 100%"]
<tbody>[TR="bgcolor: #DAE7F5"]
[TH]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
[TR]
[TH="bgcolor: #DAE7F5"]A5[/TH]
[TD="align: left"]{=IFERROR(INDEX(Sheet1!$A$2:$A$6,SMALL(IF(Sheet1!$B$2:$B$6=$A$2,ROW(Sheet1!$B$2:$B$6)-1),ROW(Sheet1!1:1))),"")}[/TD]
[/TR]
[TR]
[TH="bgcolor: #DAE7F5"]C4[/TH]
[TD="align: left"]{=IFERROR(INDEX(Sheet1!$C1:$H1,SMALL(IF(LEFT(Sheet1!$C1:$H1,3)=$B$2,COLUMN(Sheet1!$C1:$H1)),COLUMN(A$1:A$6))-2),"")}[/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]





Formulas in column A and B are dragged down. Formula in C4 is dragged to the right and the formula in C5 is dragged down and to the right. Hopefully you can adapt this to your needs.
Click to expand...

This works fine for the most part, but where there are duplicate names in sheet 1 column A, it produces incorrect data. For example:

ABCDEFGH
NameYearEng 1Eng 2Eng 3Mat1Mat2Mat3
John
May
Luke
Lucy
May

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

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

[TD="align: right"]1[/TD]
[TD="align: right"]34[/TD]
[TD="align: right"]23[/TD]
[TD="align: right"]57[/TD]
[TD="align: right"]97[/TD]
[TD="align: right"]90[/TD]
[TD="align: right"]34[/TD]

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

[TD="align: right"]1[/TD]
[TD="align: right"]34[/TD]
[TD="align: right"]76[/TD]
[TD="align: right"]87[/TD]
[TD="align: right"]34[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]90[/TD]

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

[TD="align: right"]2[/TD]
[TD="align: right"]67[/TD]
[TD="align: right"]98[/TD]
[TD="align: right"]45[/TD]
[TD="align: right"]90[/TD]
[TD="align: right"]34[/TD]
[TD="align: right"]98[/TD]

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

[TD="align: right"]3[/TD]
[TD="align: right"]98[/TD]
[TD="align: right"]67[/TD]
[TD="align: right"]45[/TD]
[TD="align: right"]67[/TD]
[TD="align: right"]86[/TD]
[TD="align: right"]90[/TD]

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

[TD="align: right"]3[/TD]
[TD="align: right"]7[/TD]
[TD="align: right"]54[/TD]
[TD="align: right"]76[/TD]
[TD="align: right"]23[/TD]
[TD="align: right"]94[/TD]
[TD="align: right"]3[/TD]

</tbody>


In the above, you can see that A3 and A6 have same names but different Years in B. But your formula retrieves A3, even when '3' is selected in A2 of sheet2. What is happening?
 
Upvote 0
When i say it works, i mean it does generate the unique list. However, i cant seem to tweak to get the rest of the data, thats why i used the formula provided by the second kind reply. But then i have the problem as mentioned above.
 
Upvote 0
With the same layout for Sheet 2 as above ...the year in cell A1 and the topic Eng 1 in cell A2 ...

in cell B2, you can test following formula : =INDEX(Data!$A$1:$I$6,MATCH(B2,Data!$B$1:$B$6,0),MATCH($A$2,Data!$A$1:$I$1,0))

HTH
 
Upvote 0
You must log in or register to reply here.

Similar threads

N
  • Solved
Total of marks with the specific conditions
2
Replies
11
Views
458
Eric W
Eric W
B
  • Question Question
I need an array formula to extract values in the first column if 2 criteria are met
Replies
2
Views
154
Banahaw2509
B
S
  • Question Question
Conditional formatting embedded within formula
Replies
3
Views
209
dreid1011
dreid1011
R
  • Solved
Ignore blanks for a SUM
Replies
2
Views
141
Rex987456
R
B
  • Solved
Single Column Formula Help With Annual Sales Agent Calculator w/CAP
Replies
6
Views
156
myall_blues
myall_blues

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