Excel VBA- Lookup value in another sheet, return corresponding columns

[jthaney10]

I know how to do what I need with XLOOKUP, but I'm dealing with about 16k lines of data and quite a few columns so I figured VBA would work best. Still new at getting through VBA code, so any help would be much appreciated.

I know I'll need to find the last row of each Workbook, and two for loops to get through all the values. Here's a simplified version of what I'm working with:

New MASTER.xlsx
	A	B	C	D	E	F
1	CPN#	TLN#	Reason	Precheck	Name	WR#
2	D1234	1234567890
3	F5432	0856912345
4	G2323	6892856936
5	E4321	1256894585
6	J1234	6975356893
7			Pull data from Old Master and place it above
New Master

Old MASTER.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L
1	Device #	SumOfCustCount	cKVAA	cKVAB	cKVAC	maxFA	Reason	Y/N	Precheck	Info	Name	WR#
2	G2323	30	0	323	0	1080	Wire Length, Cust Count	Y	Not found in Overall Checklist		ICON: J. RODRIGUEZ	10528055
3	J1234	2	0	30	0	967.12			Needs to be prechecked		2021 Assigned to ICON
4	1234567890	2	0	25	0	1149		Y	Location Fails: Reframe Pole		ICON: J. WELCH	9333699
5	F5432	1	0	25	0	1169	Wire Length, Downstream Fuse		Not found in Overall Checklist		2021 Assigned to ICON
6	9999999999	2	0	25	0	1189	Max KVA	Y	Not found in Overall Checklist		ICON: A. KAONO	9687788
Old Master

I want to look up value in col A on New and find in col A on Old. If found return col G, I, K, L from Old to col C, D, E, F on New. If not found, lookup up value in col B and return same if found. If not found, return Not Found, move to next value and repeat.

Example of Finished Product
If I went this route, I would Copy and Paste Values to remove formulas, but I think that would slow me down a ton.

New MASTER---Example of what I need.xlsx
	A	B	C	D	E	F
1	CPN#	TLN#	Reason	Precheck	Name	WR#
2	D1234	1234567890		Location Fails: Reframe Pole	ICON: J. WELCH	9333699
3	F5432	0856912345	Wire Length, Downstream Fuse	Not found in Overall Checklist	2021 Assigned to ICON
4	G2323	6892856936	Wire Length, Cust Count	Not found in Overall Checklist	ICON: J. RODRIGUEZ	10528055
5	E4321	1256894585	Not found	Not found	Not found	Not found
6	J1234	6975356893		Needs to be prechecked	2021 Assigned to ICON
Finished Example

Cell Formulas
Range	Formula
C2:C6	C2	=XLOOKUP($A2,'[VBA Example.xlsx]Old Master'!$A$2:$A$6,'[VBA Example.xlsx]Old Master'!G$2:G$6,XLOOKUP('Finished Example'!$B2,'[VBA Example.xlsx]Old Master'!$A$2:$A$6,'[VBA Example.xlsx]Old Master'!G$2:G$6,"Not found"))&""
D2:D6	D2	=XLOOKUP($A2,'[VBA Example.xlsx]Old Master'!$A$2:$A$6,'[VBA Example.xlsx]Old Master'!I$2:I$6,XLOOKUP('Finished Example'!$B2,'[VBA Example.xlsx]Old Master'!$A$2:$A$6,'[VBA Example.xlsx]Old Master'!I$2:I$6,"Not found"))&""
E2:F6	E2	=XLOOKUP($A2,'[VBA Example.xlsx]Old Master'!$A$2:$A$6,'[VBA Example.xlsx]Old Master'!K$2:K$6,XLOOKUP('Finished Example'!$B2,'[VBA Example.xlsx]Old Master'!$A$2:$A$6,'[VBA Example.xlsx]Old Master'!K$2:K$6,"Not found"))&""

 

[ThanP]

Hi, here is a simple solution with VBA

Note:
You will have to run it in you new file and change a little bit of code of the the offset, if the columns had changed.
If the CPN# or TLN# is duplicated, it will take the last one.

Option Explicit
'Set the variable
'We will assume your VBA codes are in the New Master file

'Where to look up for the data, ASSUMING they are in the same folder
Dim OldWB As Excel.Workbook
Dim OldWS As Excel.Worksheet
Dim OldRange, OldLine As Range 'To set the range to look for
Dim OldLastR As Integer 'use to set the Range to lookup for

Dim LastR, i As Integer


Sub ImportOldData()
'Set the path
Set OldWB = Excel.Workbooks("Old Master.xlsx") 'change the name of the file here
Set OldWS = OldWB.Worksheets("Old Master") 'choose the sheet here. Note, the Excel has changed.
'OldWS.Range("N1").Value = "Sucess" 'To test the "connection" works
OldLastR = OldWS.Cells(Rows.Count, 1).End(xlUp).Row 'Find the last row of the Old Master
Set OldRange = OldWS.Range("A2:A" & OldLastR) 'Set the range to look for the value in the Old Master

LastR = Cells(Rows.Count, 1).End(xlUp).Row 'To find the last items to look for

For i = 2 To LastR
For Each OldLine In OldRange
If OldLine.Value = Cells(i, 1).Value Or OldLine.Value = Cells(i, 2).Value Then
Cells(i, 3).Value = OldLine.Offset(, 6).Value 'Reason, 6 columns from the found data, like a vlookup
Cells(i, 4).Value = OldLine.Offset(, 8).Value 'Precheck, 8 columns from the found data, like a vlookup
Cells(i, 5).Value = OldLine.Offset(, 10).Value 'Name, 10 columns from the found data, like a vlookup
Cells(i, 6).Value = OldLine.Offset(, 11).Value 'WR#, 11 columns from the found data, like a vlookup
End If
Next OldLine
Next i

End Sub