Index-Match函数查找条件报错：引用单元格无法匹配数据

I’ve run into this exact issue before—manual text criteria works fine, but formula-generated criteria breaks the Index-Match. The culprit is almost always tiny, easy-to-miss differences between your generated text and the actual text in the pivot table. Here’s how to fix it:

1. Match the Date Format Exactly #

First, double-check the format code in your TEXT function. If your pivot table uses dates without leading zeros (like 3/7/2018 Total), but you’re using m/dd/yyyy in TEXT, you’ll end up with 3/07/2018 Total—the extra leading zero makes the strings look identical but they’re not.

Adjust your TEXT format to match the pivot table’s date style:

=CONCATENATE(TEXT('Performance Summary'!B1,"m/d/yyyy"), " Total")

Use m/d/yyyy for no leading zeros, or mm/dd/yyyy if the pivot table uses them (like 03/07/2018 Total).

2. Check for Hidden Spaces or Characters #

Sometimes the pivot table’s text has extra spaces (before/after "Total") or invisible characters that your formula doesn’t replicate. Use the LEN function to compare lengths:

• In a blank cell, enter LEN("3/7/2018 Total") (the working manual string)
• In another cell, enter LEN(YourFormulaCell) (the cell with your concatenated text)

If the lengths don’t match, use TRIM to clean up your generated text:

=TRIM(CONCATENATE(TEXT('Performance Summary'!B1,"m/d/yyyy"), " Total"))

TRIM removes extra leading/trailing spaces and replaces multiple spaces with one.

3. Use Wildcards for More Flexible Matching #

If you want to avoid format nitpicking, use a wildcard (*) in your MATCH criteria to match any text after the date:

=INDEX(E:E, MATCH(TEXT('Performance Summary'!B1,"m/d/yyyy")&"*", A:A, 0))

This works as long as the date portion is unique in column A (which it should be for "latest week" data).

4. Verify Pivot Table Column Format #

Make sure column A in your pivot table is formatted as Text (not Date with a custom format). If it’s a Date type with a suffix added, the underlying value is a date number, not text—so your string criteria won’t match even if it looks the same. You can check by selecting a cell in column A and looking at the formula bar: if it shows a date value instead of the full 3/7/2018 Total text, you’ll need to convert the column to text first.

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