在非相邻单元格区域使用INDIRECT函数出现#REF错误的技术咨询

Let’s break down what’s happening here and walk through the fixes:

Why Adjacent Ranges Work, But Non-Adjacent Don’t #

When you use INDIRECT("E2:E4"), Excel recognizes "E2:E4" as a valid continuous cell range reference—it’s standard syntax Excel understands, so the function pulls Miami, Paris, Rome without issues.

But when you try INDIRECT("E5,E2"), Excel doesn’t parse "E5,E2" as a valid reference. Comma-separated non-adjacent cells aren’t a format INDIRECT can interpret directly, which triggers the #REF! error.

Solution 1: Use Named Ranges (Simplest & Most Reliable) #

Named ranges let you turn non-adjacent cells into a single, recognizable reference for INDIRECT:

1. Select cell E5, hold down the Ctrl key, then select cell E2 (this picks your non-adjacent target cells).
2. Look at the Name Box (the small input field left of the formula bar), type a clear name like NonAdjacentCities, and press Enter.
3. Update the cell you’re using to represent the range (the one referenced in your INDIRECT formula) to use this named range (NonAdjacentCities) instead of "E5,E2".
4. Your INDIRECT formula will now correctly pull Amsterdam and Miami for the dropdown.

Solution 2: Dynamic Array Workaround (For Excel 365/2021) #

If you prefer not to use named ranges, you can build a dynamic array of the non-adjacent values directly. Skip INDIRECT entirely for the dropdown source and use:

=INDEX(E:E,{5,2})

This formula creates an array of the values in E5 and E2, which you can use directly as the data validation source for your dropdown.

Quick Recap #

• Continuous ranges work with INDIRECT because their text references follow standard Excel syntax.
• Non-adjacent ranges need a named range (or dynamic array workaround) to be properly recognized by Excel’s reference functions.

内容的提问来源于stack exchange，提问作者HaR