Extract multiple row values based on one cell match from another sheet input

[sugasen]

Need to extract multiple row values based on input in another sheet(SHEET2) cell by matching one column (COULMN-B IN SHEET1) cell values even repeating

sno tag size Type sign
1 E-1750 10 SB A
2 E-1750 6 SP B
3 E-1730 0.5 SB B
4 E-1750 1 SP C
5 E-1880 0.5 CP A
6 E-1750 0.5 SB B
7 E-1730 6 SP B

INPUT : E-1750 IN CELL A2 IN SHEET2 FORMULAS IN A3,A4,A5,A6 AND BELOW

OUTPUT
sno tag size type sign
1 E-1750 10 SB A
2 E-1750 6 SP B
3 E-1750 1 SP C
4 E-1750 0.5 SB B

OUTPUT TABLE SHOULD HAVE SERIAL NUMBER(sno) BY 1,2,3,4 ....

 

[Aladin Akyurek]

Book1
	A	B	C	D	E
1	sno	tag	size	Type	sign
2	1	E-1750	10	SB	A
3	2	E-1750	6	SP	B
4	3	E-1730	0.5	SB	B
5	4	E-1750	1	SP	C
6	5	E-1880	0.5	CP	A
7	6	E-1750	0.5	SB	B
8	7	E-1730	6	SP	B
Sheet2

Book1
	A	B	C	D	E
1	E-1750
2	sno	tag	size	type	sign
3	1	E-1750	10	SB	A
4	2	E-1750	6	SP	B
5	3	E-1750	1	SP	C
6	4	E-1750	0.5	SB	B
7
Sheet1

In A3 just enter and copy down:

=IF(B3="","",ROWS($1:1))

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

=IFERROR(INDEX(Sheet2!$A$2:$E$8,SMALL(IF(Sheet2!$B$2:$B$8=$A$1,ROW(Sheet2!$A$2:$E$8)-ROW(INDEX(Sheet2!$A$2:$E$8,1,1))+1),ROWS($1:1)),MATCH(B$2,Sheet2!$A$1:$E$1,0)),"")

 

[sugasen]

	A	B	C	D	E
1	sno	tag	size	Type	sign
2	1	E-1750	10	SB	A
3	2	E-1750	6	SP	B
4	3	E-1730	0.5	SB	B
5	4	E-1750	1	SP	C
6	5	E-1880	0.5	CP	A
7	6	E-1750	0.5	SB	B
8	7	E-1730	6	SP	B

<colgroup><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet2​

	A	B	C	D	E
1	E-1750
2	sno	tag	size	type	sign
3	1	E-1750	10	SB	A
4	2	E-1750	6	SP	B
5	3	E-1750	1	SP	C
6	4	E-1750	0.5	SB	B
7

<colgroup><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet1​

In A3 just enter and copy down:

=IF(B3="","",ROWS($1:1))

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

=IFERROR(INDEX(Sheet2!$A$2:$E$8,SMALL(IF(Sheet2!$B$2:$B$8=$A$1,ROW(Sheet2!$A$2:$E$8)-ROW(INDEX(Sheet2!$A$2:$E$8,1,1))+1),ROWS($1:1)),MATCH(B$2,Sheet2!$A$1:$E$1,0)),"")

Hi
Thanks for your help nd great work ,unfortunately I got error of circular reference , formula need to be corrected

Thanks

 

[Aladin Akyurek]

Hi
Thanks for your help nd great work ,unfortunately I got error of circular reference , formula need to be corrected

Thanks

Which formula?

Note that you need to map the set up correctly to your workbook:

Sheet2 >> INPUT
Sheet1 >> OUTPUT

The criterion is located in A1 of Sheet1.

 

[sugasen]

Hi Mr. Aladin
I am sorry , Formula applied as you said works very well , Thanks a lot , great work by you, LOT OF THANKS AND APPRECIATION

Regards
Kumar

 

[Aladin Akyurek]

Hi Mr. Aladin
I am sorry , Formula applied as you said works very well , Thanks a lot , great work by you, LOT OF THANKS AND APPRECIATION

Regards
Kumar

No problem. You are welcome.