Excel VBA: Match IDs Between Sheets and Copy Column F Values to Column J
Question details
Compare IDs in column B on the Update and Updating worksheets, then return the matching value from column F on Updating to column J on Update.
- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- The user needs to populate column J on the Update sheet using values associated with matching IDs stored on the Updating sheet.
- Observed behavior
- A matching ID in Update column B should receive the corresponding Updating column F value in the same row of Update column J.
Confirm that both worksheets are named exactly Update and Updating, and that the IDs in column B use the same format on both sheets. Remove unwanted leading or trailing spaces from IDs before matching if necessary.
Use XLOOKUP to return Updating column F values to Update column J
Use this option when you want a no-code, automatically updating match between the two worksheets.
Enter the formula in the first data row of Update column J, then fill it down. XLOOKUP searches the ID in Update column B within Updating column B and returns the related value from Updating column F.
Select the Update sheet tab and click the first result cell in column J, such as J2.
Type =XLOOKUP(B2,Updating!B:B,Updating!F:F,"") and press Enter. This searches for the ID in B2 on the Updating sheet and returns its column F value.
Drag the fill handle from J2 down the remaining rows, or double-click the fill handle to copy the formula beside the existing IDs in column B.
Filter or inspect blank cells in Update column J. A blank result means the ID was not found in Updating column B or its corresponding Updating column F cell is blank.
Run a VBA macro to match IDs and copy values between worksheets
Use this option when you need to perform the transfer as a repeatable macro instead of keeping formulas in column J.
Match IDs and Return Related Values in WPS Spreadsheets
WPS Spreadsheets can match IDs across worksheets with XLOOKUP and return the related value directly into the target column. Its familiar spreadsheet interface helps you migrate the same lookup workflow from Excel while preserving common workbook formats.
- 1. Open the workbook in WPS Spreadsheets: Launch WPS Office, open the workbook, and select the Update worksheet tab.
- 2. Select the target result cell: Click the first destination cell in Update column J, such as J2.
- 3. Create the ID lookup: Enter =XLOOKUP(B2,Updating!B:B,Updating!F:F,"") in J2 and press Enter.
- 4. Apply the formula to all ID rows: Use the fill handle on J2 to copy the formula down column J for the remaining records.
Frequently Asked Questions
What formula matches an ID on one sheet and returns a value from another sheet?
On the Update sheet, enter =XLOOKUP(B2,Updating!B:B,Updating!F:F,"") in J2. It matches Update!B2 against Updating column B and returns the corresponding Updating column F value.
Why does XLOOKUP return a blank cell for some IDs?
The formula returns a blank when there is no exact match in Updating column B because the fourth XLOOKUP argument is "". Check for extra spaces, different number-versus-text formats, or missing IDs.
How can I make the VBA macro match the entire ID only?
Use LookAt:=xlWhole in the Find method, as shown in the macro. This prevents a partial ID, such as 12, from matching an ID such as 123.
What happens if the Updating sheet contains duplicate IDs?
The XLOOKUP formula and the Find-based VBA macro return the first matching ID they find. Remove duplicates or define a separate rule if multiple records must be combined.
Do I need to save the workbook as XLSM when using XLOOKUP?
No. Save as .xlsx when using only formulas. Save as .xlsm only when you add and need to retain VBA macros.
