Function Problems

How to Use a VLOOKUP Range Named in Another Cell in Excel and WPS

Partner Editor Oct 10, 2026 869 views

Question details

The user wants to use a named range in a VLOOKUP formula, but the range name is stored as a text string in another cell, causing the formula to fail.

How to Use a VLOOKUP Range Named in Another Cell

Product: Spreadsheet
Device & OS: not provided
Scenario: Creating dynamic VLOOKUP formulas that pull data from different named ranges depending on the text value entered in a specific reference cell.
Observed behavior: The VLOOKUP formula returns an #N/A error because it interprets the referenced cell's content as a simple text string instead of a valid named range array.

Before you start

Verify that your named ranges are correctly defined in your workbook's Name Manager and that the text in your referencing cell exactly matches the defined range name.

Solution 1Recommended

Use the INDIRECT Function to Convert Text into a Reference

Nesting the INDIRECT function inside your VLOOKUP formula translates a text string from a cell into a valid named range reference.

When you type a cell reference (like K4) into the table_array argument of a VLOOKUP, Excel and WPS Spreadsheet read the literal text inside K4. If K4 contains the word 'SalesData', VLOOKUP tries to look inside the word 'SalesData' rather than the named range called SalesData. The INDIRECT function resolves this by evaluating the text string and converting it into a mathematical reference to the named array.

Select the destination cell

Click on the cell where you want the VLOOKUP result to appear and type the beginning of your formula: =VLOOKUP(

Enter the lookup value

Select the cell containing the value you want to search for, or type its reference (for example, $C$2), and add a comma.

Add the INDIRECT function

Instead of typing the named range, type INDIRECT( followed by the cell reference that contains the text of your named range (for example, K4). Close the parenthesis for INDIRECT and add a comma.

Complete the VLOOKUP formula

Type the column index number (e.g., 2), followed by a comma, and FALSE for an exact match. The formula should look like: =VLOOKUP($C$2, INDIRECT(K4), 2, FALSE).

Handle blank cells (Optional)

To prevent errors when the reference cell is empty, wrap the formula in an IF statement: =IF(ISBLANK(C4), 0, VLOOKUP($C$2, INDIRECT(K4), 2, FALSE)). Press Enter to apply.

Use the INDIRECT Function to Convert Text into a Reference

Exact Match Requirement: Setting the last argument of your VLOOKUP formula to FALSE ensures you are finding an exact match, which is critical when working with dynamic referenced arrays.

Advanced Data Management

Easily Handle Dynamic VLOOKUPs with WPS Spreadsheet

WPS Spreadsheet natively supports advanced functions like VLOOKUP and INDIRECT, empowering you to build dynamic, complex data models with ease.

1. Open your workbook: Launch WPS Spreadsheet and open the document containing your data.
2. Define your ranges: Navigate to the Formulas tab and click on Name Manager to create your named ranges.
3. Apply the INDIRECT formula: Enter =VLOOKUP(C2, INDIRECT(K4), 2, FALSE) into your target cell, adjusting the cell references as needed.
4. Calculate and evaluate: Press Enter. WPS Spreadsheet will instantly convert the text to a range and fetch your data.

100% compatibility with Microsoft Excel formulas, functions, and formattingIntuitive Name Manager to effortlessly define, edit, and track your named rangesLightweight performance that calculates complex INDIRECT formulas quickly

microsoft office alternative - wps office

Frequently Asked Questions

Why does VLOOKUP return a #REF! error when I use INDIRECT?

A #REF! error usually occurs if the text in your reference cell does not perfectly match an existing named range, contains spaces, or if the named range refers to a closed external workbook.

Can my named range contain spaces if I use INDIRECT?

No. Named ranges in spreadsheet applications cannot contain spaces. If the text in your cell includes spaces, it will not match a valid named range, causing the INDIRECT function to fail.

Will using the INDIRECT function slow down my spreadsheet?

INDIRECT is a volatile function, meaning it recalculates every time any change is made to the workbook. If used extensively across thousands of rows, it may cause a noticeable decrease in calculation speed.

Excel & Data Problems