VBA & Macro Problems

How to Convert an XLOOKUP Formula to a VBA Value-Only Result in Excel

Camila Milosovich Oct 23, 2026 869 views

Question details

Use VBA to match project and product values across two workbooks using XLOOKUP, and replace the dynamic formula with a static value.

How to Convert an XLOOKUP Formula to a VBA Value-Only Result

Product: Spreadsheet
Device & OS: not provided
Scenario: Looking up completion dates across two separate workbooks based on project and product matching criteria without leaving active formulas in the cells.
Observed behavior: Dynamic formulas can accidentally update or break when external workbooks change. The goal is to retain only the returned values and prevent future workbook updates from altering the results.

Before you start

Ensure that both the source and destination workbooks are open before running the macro to prevent external reference errors.

Solution 1Recommended

Use the .Value Property to Replace XLOOKUP Formulas

By applying the .Formula property followed immediately by the .Value = .Value technique within a With statement, you can evaluate the XLOOKUP and lock in the static results simultaneously.

This method temporarily places the XLOOKUP formula into the target cells to pull data from a secondary workbook, and then overwrites those cells with the calculated results. This eliminates the risk of data changing later if the source workbook is moved or modified.

Open the VBA Editor

Press Alt + F11 on your keyboard, or navigate to the Developer tab and click on 'Visual Basic' to launch the VBA Editor.

Insert a New Module

In the Project Explorer window, right-click on your workbook, hover over 'Insert', and select 'Module'.

Write the Formula Assignment Code

Use a With statement to apply the XLOOKUP formula to your target range. For example: With Range("BD8:BD" & N): .Formula = "=XLOOKUP(RC[-55]&RC[-54],'[Workbook2]Sheet1'!R5C1:R50000C1&'[Workbook2]Sheet1'!R5C2:R50000C2,'[Workbook2]Sheet1'!R5C7:R50000C7,\"\")"

Convert the Range to Static Values

Immediately after assigning the formula on the same block, append the value assignment code: : .Value = .Value: End With. This will overwrite the formulas with the evaluated text or numbers.

Run the Macro

Press F5 or click the 'Run' button in the toolbar to execute the code. Your target column will now display the completion dates as static values.

Use the .Value Property to Replace XLOOKUP Formulas

Performance Optimization: Converting large sets of lookup formulas to static values significantly reduces file size and prevents slow calculation speeds in large datasets.

Efficient VBA Handling

Run Complex XLOOKUP Macros Seamlessly in WPS Spreadsheet

WPS Spreadsheet fully supports advanced VBA scripting, enabling you to automate cross-workbook lookups and instantly convert array formulas to static values without lag.

1. Open WPS Spreadsheet: Launch WPS Spreadsheet and open the two workbooks you need to link and extract data from.
2. Access Developer Tools: Navigate to the Developer tab and open the VBA Editor to create your macro.
3. Execute the Macro: Run your customized VBA code to insert the XLOOKUP formula and freeze the values automatically.

Fully compatible with Microsoft Excel VBA macros and .xlsm formatsNative support for modern lookup formulas including XLOOKUP and XMATCHOptimized processing engine for handling large data ranges efficiently

microsoft office alternative - wps office

Frequently Asked Questions

Can I use this VBA method to replace VLOOKUP or other formulas as well?

Yes, the .Value = .Value property technique works universally for any formula you insert via VBA, including VLOOKUP, INDEX/MATCH, and custom mathematical functions.

Will replacing formulas with values reduce my workbook file size?

Yes, replacing complex, array-based formulas (like multi-criteria XLOOKUPs spanning thousands of rows) with static values significantly decreases the overall file size and improves calculation speed.

Why am I getting an error when referencing the second workbook in VBA?

Ensure that the second workbook is open while running the macro. Additionally, verify that the workbook name, sheet names, and cell references in your VBA .Formula string perfectly match the external workbook.

Excel & Data Problems