Fix INDIRECT Formula Returning #VALUE! Inside IF Statement in Excel
Question details
The user is experiencing a #VALUE! error when using the INDIRECT function nested inside an IF formula to dynamically reference and compare cells in different columns.

- Product
- Excel
- Device & OS
- not provided
- Scenario
- Building dynamic cell references within an IF statement for data comparison across different columns.
- Observed behavior
- The formula evaluates to a #VALUE! error instead of returning the expected logical result.
Before troubleshooting the formula, ensure that the helper cells containing column letters or row numbers do not contain invisible trailing spaces or formatting errors that could invalidate the text string.
Verify and Isolate the Dynamic Cell References
Break down the formula to ensure the text strings provided to the INDIRECT function successfully evaluate to valid cell addresses.
The most common reason an INDIRECT function fails with a #VALUE! error is that the string it constructs does not map to a real cell address. Testing the components of your nested formula independently will reveal where the logic breaks down.
Inspect the helper cells containing your column letters (e.g., DQ2, DR2) for accidental leading or trailing spaces. Select the cell, press F2, and delete any blank spaces before or after the text.
In a blank cell, type the concatenation part of your formula (for example, =DQ2&ROW()) and press Enter. Verify that the result looks exactly like a standard cell address, such as 'A2' or 'DQ5'.
Wrap your tested string in the INDIRECT function by typing =INDIRECT(DQ2&ROW()) in an empty cell. Press Enter to see if it successfully fetches the target cell's value without throwing a #VALUE! or #REF! error.
Once you confirm both INDIRECT functions are returning the correct values, combine them back into your IF formula (e.g., =IF(INDIRECT(DQ2&ROW())=INDIRECT(DR2&ROW()), "Match", "No Match")) to perform your logical comparison.

Evaluate and Debug Formulas Easily in WPS Office
WPS Spreadsheet offers powerful formula auditing tools that help you easily track down errors like #VALUE! in complex, nested functions such as IF and INDIRECT.
- 1. Open your file: Launch WPS Spreadsheet and open the workbook containing the formula error.
- 2. Access formula auditing: Select the cell with the #VALUE! error, then navigate to the 'Formulas' tab on the top ribbon.
- 3. Evaluate the formula: Click on 'Evaluate Formula' to open a dialog box that lets you step through your IF and INDIRECT functions piece by piece.
- 4. Locate the error: Click 'Evaluate' repeatedly to identify exactly which part of the nested formula is generating the #VALUE! error, then update the cell reference accordingly.

Frequently Asked Questions
What does a #VALUE! error mean when using INDIRECT?
A #VALUE! error generally means a formula has the wrong type of argument. For the INDIRECT function specifically, it usually indicates that the text string provided cannot be translated into a valid cell reference.
Can I use INDIRECT to reference cells in another worksheet?
Yes. However, if your sheet name contains spaces, you must include single quotes around the sheet name within your formula. For example: =INDIRECT("'"&A1&"'!B2").
How can I automatically remove hidden spaces from reference cells?
You can use the TRIM function to strip extra spaces from text. By modifying your reference to include TRIM, like =INDIRECT(TRIM(A1)&"2"), you ensure that accidental spaces don't corrupt the cell address.
Why does my INDIRECT formula only work sometimes?
If your INDIRECT formula references a different workbook, that external workbook must be open. If the external file is closed, the INDIRECT function will automatically return a #REF! or #VALUE! error.




