Search 1 information and getting multiple results from various sheets

[Farley12]

Hi,

I would like to search for Item 001, I need the search to be carried out on multiple sheets.
Then, the results will be displayed in tables including the other related information.

I watched several videos but still had errors in my excel:
Currently i'm still searching information from 1 sheet only

=IF(ROWS(B$6:B6)<=$C$3,INDEX('2018'!A$2:A$11,SMALL(IF('2018'!$A$2:$A$11=$B$3,ROW('2018'!$A$2:$A$11)-ROW('2018'!$A$2)+1),ROWS(B6:B6))),"")

(Bracket showing the row and column)
[TABLE="width: 314"]
<colgroup><col><col><col></colgroup><tbody>[TR]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
Search: Item 1 (Multiple data from various sheets)

[TABLE="width: 250"]
<colgroup><col><col></colgroup><tbody>[TR]
[TD] Item[/TD]
[TD]Count[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]4 (C3)[/TD]
[/TR]
</tbody>[/TABLE]

[TABLE="width: 568"]
<colgroup><col><col><col><col></colgroup><tbody>[TR]
[TD](A2)[/TD]
[TD]1[/TD]
[TD]ABC[/TD]
[TD]Results[/TD]
[TD]4[/TD]
[/TR]
[TR]
[TD]A3[/TD]
[TD]2[/TD]
[TD]123[/TD]
[TD]Multiple[/TD]
[TD]4[/TD]
[/TR]
[TR]
[TD]A4[/TD]
[TD]3[/TD]
[TD]DEF[/TD]
[TD]Excel[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]A5[/TD]
[TD]1[/TD]
[TD]987[/TD]
[TD]Error[/TD]
[TD]4[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]5[/TD]
[TD] JBG [/TD]
[TD]Results[/TD]
[TD]4[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]6[/TD]
[TD] ABD [/TD]
[TD]Multiple[/TD]
[TD]4[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]2[/TD]
[TD]435[/TD]
[TD]Excel[/TD]
[TD]4[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]1[/TD]
[TD]543[/TD]
[TD]Error[/TD]
[TD]4[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]2[/TD]
[TD]DSW[/TD]
[TD]Results[/TD]
[TD]4[/TD]
[/TR]
[TR]
[TD]A11[/TD]
[TD]1[/TD]
[TD]#$%[/TD]
[TD]Multiple[/TD]
[TD]4[/TD]
[/TR]
</tbody>[/TABLE]


Results that I want:

[TABLE="width: 256"]
<colgroup><col width="64" span="4" style="width:48pt"> </colgroup><tbody>[TR]
[TD="class: xl68, width: 64"]B6[/TD]
[TD="class: xl68, width: 64"]1[/TD]
[TD="class: xl66, width: 64"]ABC[/TD]
[TD="class: xl66, width: 64"]Results[/TD]
[TD="class: xl66, width: 64"]4[/TD]
[/TR]
[TR]
[TD="class: xl69"]B7[/TD]
[TD="class: xl69"]1[/TD]
[TD="class: xl67"]987[/TD]
[TD="class: xl67"]Error[/TD]
[TD="class: xl66"]4[/TD]
[/TR]
[TR]
[TD="class: xl68"]B8[/TD]
[TD="class: xl68"]1[/TD]
[TD="class: xl66"]543[/TD]
[TD="class: xl67"]Error[/TD]
[TD="class: xl66"]4[/TD]
[/TR]
[TR]
[TD="class: xl68"]B9[/TD]
[TD="class: xl68"]1[/TD]
[TD="class: xl66"]#$%[/TD]
[TD="class: xl66"]Multiple[/TD]
[TD="class: xl66"]4[/TD]
[/TR]
</tbody>[/TABLE]
[TABLE="width: 500"]
<tbody></tbody>[/TABLE]



Results that I get:

[TABLE="width: 568"]
<colgroup><col><col><col><col></colgroup><tbody>[TR]
[TD]B6[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]B7[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]B8[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]B9[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
How to fix it or how to do it in different way?
Any links or videos that suits my situations?

Thank you.

 

[Aladin Akyurek]

Sheet: 2018

Book1
	A	B	C	D
1
2	1	ABC	Results	4
3	2	123	Multiple	4
4	3	DEF	Excel	2
5	1	987	Error	4
6	5	JBG	Results	4
7	6	ABD	Multiple	4
8	2	435	Excel	4
9	1	543	Error	4
10	2	DSW	Results	4
11	1	#$%	Multiple	4
2018

Sheet: Sheet1

Book1
	A	B	C	D	E
1	Item	1
2	Count	4
3
4	1	1	ABC	Results	4
5	4	1	987	Error	4
6	8	1	543	Error	4
7	10	1	#$%	Multiple	4
8
Sheet1

In B2 just enter:

=COUNTIFS('2018'!A2:A11,B1)

In A4 control+shift+enter, not just enter, and copy down:

=IF(ROWS($A$4:A4)>$B$2,"",SMALL(IF('2018'!$A$2:$A$11=$B$1,ROW('2018'!$A$2:$A$11)-ROW(INDEX('2018'!$A$2:$A$11,1,1))+1),ROWS($A$4:A4)))

In B4 just enter, copy across, and down:

=IF($A4="","",INDEX('2018'!A$2:A$11,$A4))


Note. This set up does not apply for an across multiple sheets setting.