Find J2 in Sheet1 and copy entire row to Sheet2

[Madame]

Hi,

I did my homework and researched every formula there is online. Nothing works for me. It should be so simple:

In Sheet1 find J2 (copy(able) down to J3, J4, etc.)

Grab entire row of where J2 value was found in Column 9 and copy to Sheet2

(Also, grab ALL occurrences of J2 in Column 9)

I have an abundant amount of J2's and need it to be a formula rather than VBA I think

Visual:

Source Data on Sheet1:

	A	B	C	D
1	Make	Model	Color	SN
2	HP	OJ	green	1
3	Canon	IR	blue	2
4	Lexmark	Ju	beige	3

<colgroup><col width="64" span="5" style="width:48pt"> </colgroup><tbody>
</tbody>

<colgroup><col width="64" span="4" style="width:48pt"></colgroup><tbody><!--EndFragment--></tbody> Template on Sheet2: Enter SN: 2 Enter SN query produces: Canon, IR, blue, 2 I know there are a ton of vba's and formulas out there but none worked for me. Can anybody guess where I am turning wrong? I'd be eternally grateful! TY!

<colgroup><col width="64" span="4" style="width: 48pt;"></colgroup><tbody>
</tbody>

<colgroup><col width="64" span="4" style="width:48pt"></colgroup><tbody>
</tbody>

 

[Madame]

I found this example below that almost does what I want. Except it doesn't find all rows and you can't copy it down. Would it be easier to fix this formula?
=INDEX(C6:C14,MATCH($B3,$B$6:$B$14,0))

ID	Street	City	State
5	Full	SF	CA
2	Sun	MIA	FL
1	Main	LA	CA
2	Park	SF	CA
3	Sun	MIA	FL
4	Moon	Seattle	WA
5	Full	SF	CA
2	Park	SD	CA

<colgroup><col width="64" span="4" style="width:48pt"> </colgroup><tbody>
</tbody>

Hi,

I did my homework and researched every formula there is online. Nothing works for me. It should be so simple:

In Sheet1 find J2 (copy(able) down to J3, J4, etc.)

Grab entire row of where J2 value was found in Column 9 and copy to Sheet2

(Also, grab ALL occurrences of J2 in Column 9)

I have an abundant amount of J2's and need it to be a formula rather than VBA I think

Visual:

Source Data on Sheet1:

	A	B	C	D
1	Make	Model	Color	SN
2	HP	OJ	green	1
3	Canon	IR	blue	2
4	Lexmark	Ju	beige	3

<tbody>
</tbody>

Template on Sheet2: Enter SN: 2 Enter SN query produces: Canon, IR, blue, 2 I know there are a ton of vba's and formulas out there but none worked for me. Can anybody guess where I am turning wrong? I'd be eternally grateful! TY!

<tbody>
</tbody>

<tbody>
</tbody>

 

[CsJHUN]

Is it possible to use autofilter?

 

[Madame]

Hi,

Thank you for your answer. Not really as I have hundreds of constantly changing serial numbers to check against and input like this ("1", "3") would to too much work. Good thinking tho!

 

[CsJHUN]

I meant, if you can set an autofilter above the data table you can filter columns for *1*, or anything else you want, than copy the filtered data to elsewhere (w/ macro ofc)