Compare two sheets for a match and copy entire row

[htou]

Hello again,

Ive been googling for a result and I cant seem to get the right solution so im back for some help.

Worksheet 1 has data in a single column, range A1:A615 (Range A). Worksheet 2 contains data across columns and rows, range A1:AL1003 (Range B). Where data in Range A matches data in a specific column in Range B; I would like copy the entire row. The specific Column in Range B is Column F.

I started with <i>Vlookups</i> but i couldn't get it to work for multiple columns. Read about <i>Match</i> and <i>Index</i> but the samples ive looked at online werent exactly working for me. Is this do-able with formulas?

Better explained with an example.
Data in Sheet 1 - Sample (actual range is A1:A615)

Excel 2010
	A
1	101B2911
2	102B2911
3	102N6211
4	102USL39717
5	103B2911
Sheet1

Data in Sheet 2 - Sample (actual range is A1:AL1003, includes headings)

Excel 2010
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
1	FID	Shape	PCCC_pre	LID	CID	LIDCID	PIT	Num	PITNum	PAR_IND	AREA	EXCL_AREA	VOLUME	IND	PRC
2	143	Polygon	901	102	USL39717	102USL39717	36899	5	36899005	0	237000	0	0	N	3290
3	124	Polygon	818	103	USL39717	103USL39717	36899	6	36899006	0	5670	0	0	N	3290
4	130	Polygon	829	12	B2263	12B2263	39717	27	39717027	0	607	0	0	Y	3290
5	131	Polygon	830	19	B2263	19B2263	39717	20	39717020	0	650	0	0	Y	3290
6	140	Polygon	893	8	USL39395	8USL39395	39395	8	39395008	0	130000	0	0	N	1405
7	557	Polygon	474	101	B2911	101B2911	38974	44	38974044	0	1012	0	0	Y	5227
8	619	Polygon	561	102	B2911	102B2911	38974	45	38974045	0	1012	0	0	Y	5227
9	844	Polygon	873	103	B2911	103B2911	38974	93	38974093	0	1012	0	0	Y	5227
10	134	Polygon	885	7	FD1025	7FD1025	60342	5	60342005	0	16410	0	0	Y	799
11	126	Polygon	825	102	N6211	102N6211	2356	52	2356052	0	2474	0	0	Y	1953
12	132	Polygon	833	305	G9661	305G9661	2357	84	2357084	0	809	0	0	Y	1953
13	135	Polygon	888	2	F9344	2F9344	2356	105	2356105	0	784	0	0	Y	1953
14	136	Polygon	889	5	F9343	5F9343	2356	151	2356151	0	809	0	0	Y	1953
15	137	Polygon	890	12	F9344	12F9344	2356	115	2356115	0	711	0	0	Y	1953
16	138	Polygon	891	304	G9661	304G9661	2357	83	2357083	0	878	0	0	Y	1953
17	139	Polygon	892	209	G9661	209G9661	2357	52	2357052	0	956	0	0	Y	1953
18	141	Polygon	895	193	USL2356	193USL2356	2356	133	2356133	0	30900	0	0	N	1953
19	142	Polygon	897	138	USL2356	138USL2356	2356	138	2356138	0	5350	0	0	N	1953
20	133	Polygon	880	29	DS546	29DS546	36440	3	36440003	0	37500	0	0	N	4441
Sheet2

Essentially the result im chasing. Where data in Column F in sheet 2 matches data in Range A, the entire row is copied to adjoining cell-

Excel 2010
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P
1	101B2911	557	Polygon	474	101	B2911	101B2911	38974	44	38974044	0	1012	0	0	Y	5227
2	102B2911	619	Polygon	561	102	B2911	102B2911	38974	45	38974045	0	1012	0	0	Y	5227
3	102N6211	126	Polygon	825	102	N6211	102N6211	2356	52	2356052	0	2474	0	0	Y	1953
4	102USL39717	143	Polygon	901	102	USL39717	102USL39717	36899	5	36899005	0	237000	0	0	N	3290
5	103B2911	844	Polygon	873	103	B2911	103B2911	38974	93	38974093	0	1012	0	0	Y	5227
Sheet1

 

[mas123]

Put this formula in cell B2 in sheet1

=INDEX(Sheet2!$A$1:$AL$1003,MATCH($A1,$A$1:$A$615,0),COLUMN(A$1))

copy formula over and down

 

[htou]

When i copy down and across the formula it "copies" everything from sheet 2. It doesn't necessarily copy the row containing the matched value only.

A small sample of the results

Excel 2010
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P
1	101B2911	FID	Shape	PCCC_pre	LID	CID	LIDCID	PIT	Num	PITNum	PAR_IND	AREA	EXCL_AREA	VOLUME	IND	PRC
2	102B2911	0	Polygon	5	128	NPW700	128NPW700	39718	38	39718038	0	86400000	0	0	N	3290
3	102N6211	1	Polygon	6	128	NPW700	128NPW700	39386	5	39386005	0	86400000	0	0	N	1405
4	102USL39717	2	Polygon	12	20	AP21053	20AP21053	2356	192	2356192	0	8830	0	0	N	1953
5	103B2911	3	Polygon	26	17	SP197908	17SP197908	2294	641	2294641	0	10360	0	0	Y	202
6	103N6211	4	Polygon	28	2	SP261161	2SP261161	2294	709	2294709	0	11710	0	0	Y	202
7	103USL39717	5	Polygon	59	6	SP200837	6SP200837	39723	679	39723679	0	5870000	0	0	Y	3374
8	104B2261	6	Polygon	68	2	SP197908	2SP197908	15325	135	15325135	0	7182	0	0	Y	202
Sheet1

 

[mas123]

Oh really
the last formula was without making example
but now I copied the example to sheet
this is the true formula:

=INDEX(Sheet2!$A$1:$AL$1003,MATCH($A1,Sheet2!$F$1:$F$1003,0),COLUMN(A$1))

try it and tell me the results

 

[htou]

Awesome mas123, you sir are a legend...unless you're female then you are a goddess!

Its funny i was so close to getting this formula to work myself last night then my PC shut-down. I gave up and went to sleep. Thank you again