在INDEX函数中使用INDIRECT函数的公式错误排查

Let's break down the issue with your formula and get it working properly. Your current formula is:

=INDEX(INDIRECT($H$4),MATCH($A3,INDIRECT($F$4),0),2)

First, let's rule out the most common pitfalls when using INDIRECT with named ranges:

• Confirm your named ranges are structured correctly:

  • The range referenced by $H$4 must be a multi-column range (since you're trying to pull column 2). If it's only 1 column, you'll get a #REF! error here. Double-check the named range's definition in Name Manager.
  • Ensure the named range in $F$4 is a single-column range of dates that matches the format of values in $A3 (no hidden spaces or date format mismatches that would break MATCH).

• Fix the INDIRECT syntax for sheet-specific named ranges:
  If your named ranges are worksheet-specific (not workbook-level), INDIRECT needs the full reference including the sheet name. For example, if the named range SalesData is tied to Sheet2, $H$4 should contain Sheet2!SalesData instead of just SalesData. Workbook-level named ranges don't need this prefix.

• Simplify with a more robust formula:
  Instead of nesting INDIRECT inside INDEX, you can split the column reference to make it clearer and avoid potential array issues. Try this modified formula:

  =XLOOKUP($A3, INDIRECT($F$4), INDEX(INDIRECT($H$4),,2))
  

  INDEX(INDIRECT($H$4),,2) explicitly targets the second column of your data range, and XLOOKUP handles the match more reliably than INDEX/MATCH in many cases.

• Troubleshoot step-by-step:

  1. Test =INDIRECT($H$4) in a blank cell—if it returns #REF!, your named range name in $H$4 is invalid or the range doesn't exist.
  2. Test =MATCH($A3, INDIRECT($F$4),0)—if it returns #N/A, the date in $A3 isn't present in the $F$4 named range, or there's a format mismatch.

Once you verify these pieces, your formula should correctly pull data from the user-selected named ranges to populate your chart.

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