Function Problems

Fix Office Script XLOOKUP Formula Returning a #NAME Error in Excel

Muhammad Talha Oct 1, 2026 871 views

Question details

The user wants to prevent Office Script from adding quotation marks around XLOOKUP lookup references, which causes a #NAME error.

How to Fix Office Script XLOOKUP Formula #NAME Error

Product: Microsoft Excel
Device & OS: not provided
Scenario: Automating spreadsheet tasks by writing an XLOOKUP formula to a cell using an Office Script.
Observed behavior: The script places quotation marks around the lookup reference, turning it into a text string and resulting in a #NAME error in the cell.

Before you start

Ensure you have editing access to your Office Script and note the exact relative cell ranges needed for your XLOOKUP parameters.

Solution 1Recommended

Use R1C1 Relative References in Office Script

Replace the standard formula method with R1C1 reference notation to prevent the script engine from interpreting the cell reference as a text string.

When passing dynamic cell references via Office Scripts, standard A1 notation can sometimes get inadvertently wrapped in quotes by the compiler. Using R1C1 notation ensures the lookup value is treated strictly as a relative cell reference, avoiding the #NAME error.

Open the Code Editor

Navigate to the Automate tab on your Excel ribbon and open the Code Editor for your specific Office Script.

Locate the formula script line

Find the line of code where you apply the XLOOKUP formula to the cell range using the setFormula method.

Change to setFormulaR1C1

Replace setFormula with setFormulaR1C1 and update the formula string to use R1C1 notation. For example: selectedSheet.getRange("Z2").setFormulaR1C1("=XLOOKUP(R[0]C[-1],R2C28:R6C28,R2C29:R6C29,0,-1)");

Save and run the script

Save the changes to your script and run it. The formula will now populate in cell Z2 without quotation marks, successfully looking up the correct value.

Use R1C1 Relative References in Office Script

Understanding R1C1 Syntax: In the R1C1 format, R[0]C[-1] refers to the cell in the same row (0 offset) and one column to the left (-1 offset) of the target range.

Free Microsoft Office alternative

Handle Complex Formulas and Macros with WPS Office

If you frequently encounter script compatibility or formula errors in spreadsheet software, consider WPS Office. It provides robust support for modern formulas like XLOOKUP and offers an intuitive built-in JS Macro editor for seamless automation.

Free to use, lightweight, and fast to install on any operating system.Fully compatible with Microsoft Excel (.xlsx) formats and modern formulas including XLOOKUP.Built-in VBA and JavaScript Macro support for powerful spreadsheet automation.Familiar user interface requiring zero learning curve for rapid productivity.

microsoft office alternative - wps office

Frequently Asked Questions

Why does XLOOKUP return a #NAME error?

A #NAME error occurs when a spreadsheet application does not recognize text in a formula. In scripted formulas, this typically happens if cell references are accidentally wrapped in quotation marks, forcing the engine to read them as undefined text strings instead of actual cell addresses.

What is the difference between setFormula and setFormulaR1C1?

The setFormula method applies a standard A1-style formula (like A2), while setFormulaR1C1 uses row and column numerical offsets (like R[0]C[-1]). Using R1C1 is highly recommended in scripting because it correctly maintains relative references without hardcoding text strings that break formulas.

Can I manually fix the #NAME error in the spreadsheet?

Yes. You can click on the affected cell, remove the quotation marks around the lookup reference directly in the formula bar, and press Enter. However, modifying the script to use R1C1 notation permanently prevents the issue from happening in future script executions.

Excel & Data Problems