Copy data based on one column to another column based on another column

[daloudin]

For each value in Column B of Sheet 2 I want to find that value in Column A of Sheet 1 and paste the value from Column A of Sheet 2 in Column C of Sheet 1...

Sheet 2

Sheet 1

 

[VoG]

Hello and welcome to MrExcel.

Try using Excel Jeanie http://www.excel-jeanie-html.de/html/hlp_schnell_en.php to post shots of what you have and what you want to achieve. Please also provide a better description.

 

[daloudin]

We are converting the id's in our database and the output files used by other departments need a cross reference. Rather than do it all by hand I'm trying to get Excel to find the matching information and paste the data for me. Their data is in Sheet 2 with a column called "NEW" that has text in it that matches up to our "LN700" column in Sheet 1.

For each value in the "NEW" column of Sheet 2 I want to find that exact value in the "LN700" column of Sheet 1 and then copy the value beside the "NEW" column of Sheet 2 ("OLD") in to the "OLD" column of Sheet 1.

Once this is done I can sort on Column C in Sheet 1 and export them a txt list for use in their processes.

Excel Workbook
	A	B	C
1	LN700	Tagname
2	AL1912AM	AM_875
3	AL1912AMP	AmpsB_022
4	AL1912FL311	FL311_002
5	AL1912FL321	FL321_002
6	AL1912FL351	FL351_063
7	AL1912FLME	FLPSI_004
8	AL1912FLTP	FLDC_077
9	AL1912LK	LK_283
10	AL1912LR	LR_554
11	AL1912MCOMM	MComm_002
12	AL1912OC	AL_1912
13	AL1912RCOMM	RComm_062
14	AL1912VARS	MVAR_087
15	AL1912WATT	MW_020
16	AL1922AAMP	AmpsA_012
17	AL1922AM	AM_872
18	AL1922BAMP	AmpsB_019
19	AL1922C1	FLPSI_002
20	AL1922CAMP	AmpsC_012
21	AL1922FLDC	FLDC_075
22	AL1922LK	LK_280
23	AL1922LR	LR_551
24	AL1922OC	AL_1922
25	AL1922VARS	MVAR_084
26	AL1922WATT	MW_017
27	AL1932AAMP	AmpsA_013
28	AL1932AM	AM_873
29	AL1932BAMP	AmpsB_020
30	AL1932CAMP	AmpsC_013
31	AL1932FLDC	FLDC_076
32	AL1932FLME	FLPSI_003
Sheet1

Excel Workbook
	A	B
1	OLD	NEW
2	AL1932AAV	AL1932AAMP
3	AL1932BAV	AL1932BAMP
4	AL1932CAV	AL1932CAMP
5	AL1932UAV	AL1932UAMP
6	AL1932VARV	AL1932VARS
7	AL1932WATV	AL1932WATT
8	ALBUS2AGVV	ALBUS2AGVB
9	ALBUS2BGVV	ALBUS2BGVB
10	ALBUS2CGVV	ALBUS2CGVB
11	ALBUS2VARV	ALBUS2VARS
12	ALBUS2WATV	ALBUS2WATT
13	ARBUS3VLTV	ARBUS3AGV
14	ARBUS3VARV	ARBUS3VARS
15	ARBUS3WATV	ARBUS3WATT
16	AREUAMPV	AREUAMP
17	AREVR1AMPV	AREVR1AMP
18	AREVR1PFV	AREVR1VAR
19	AREVR1VLTV	AREVR1VLT
20	AREVR2AMPV	AREVR2AMP
21	AREVR2PFV	AREVR2VAR
22	AREVR2VLTV	AREVR2VLT
23	AREVR3AMPV	AREVR3AMP
24	AREVR3PFV	AREVR3VAR
25	AREVR3VLTV	AREVR3VLT
26	ARFUAMPV	ARFUAMP
27	ARFVR1AMPV	ARFVR1AMP
28	ARFVR1PFV	ARFVR1VAR
29	ARFVR1VLTV	ARFVR1VLT
30	ARFVR2AMPV	ARFVR2AMP
31	ARFVR2PFV	ARFVR2VAR
32	ARFVR2VLTV	ARFVR2VLT
Sheet2

 

[daloudin]

...and an example of what it would look like afterwards:

Excel Workbook
	A	B	C
1	LN700	Tagname	OLD
2	AL1912AM	AM_875
3	AL1912AMP	AmpsB_022
4	AL1912FL311	FL311_002
5	AL1912FL321	FL321_002
6	AL1912FL351	FL351_063
7	AL1912FLME	FLPSI_004
8	AL1912FLTP	FLDC_077
9	AL1912LK	LK_283
10	AL1912LR	LR_554
11	AL1912MCOMM	MComm_002
12	AL1912OC	AL_1912
13	AL1912RCOMM	RComm_062
14	AL1912VARS	MVAR_087
15	AL1912WATT	MW_020
16	AL1922AAMP	AmpsA_012
17	AL1922AM	AM_872
18	AL1922BAMP	AmpsB_019
19	AL1922C1	FLPSI_002
20	AL1922CAMP	AmpsC_012
21	AL1922FLDC	FLDC_075
22	AL1922LK	LK_280
23	AL1922LR	LR_551
24	AL1922OC	AL_1922
25	AL1922VARS	MVAR_084
26	AL1922WATT	MW_017
27	AL1932AAMP	AmpsA_013	AL1932AAV
28	AL1932AM	AM_873
29	AL1932BAMP	AmpsB_020	AL1932BAV
30	AL1932CAMP	AmpsC_013	AL1932CAV
31	AL1932FLDC	FLDC_076
32	AL1932FLME	FLPSI_003
33	AL1932LK	LK_281
Sheet1

 

[VoG]

In C2 of Sheet1 and copied down try

=IF(ISNUMBER(MATCH(A2,Sheet2!B:B,0)),INDEX(Sheet2!A:A,MATCH(A2,Sheet2!B:B,0)),"")

 

[daloudin]

Perfect. Thank you... I got hung up on VLookUp and never thought about a straight logical tie. This worked perfect.