#NO MATCH in a index match formula for some values

I have a "filter" table in which I can choose via dropdown "year" and "month" values. By updating the values there, I can update the values in other small table I plan to select individual values considering two labels, "On Time" and "Delayed".

When I use the following formula, I get a #NO MATCH for certain months, but all months should be retrieved even if they have 0s because they are included in the table:

=INDEX($[5]$102:$[10]$125, MATCH("Delayed" + [24]251, $[2]$102:$[2]$125), MATCH($[23]$251, $[5]$101:$[10]$101))

Where the $[5]$102:$[10]$125 looks for the values to collect; MATCH("Delayed" + [24]251, $[2]$102:$[2]$125) checks the concatenation in column "2" that is compared to the filter table I have with the month and the label (in this case "Delayed") to get the position for the rows; and MATCH($[23]$251, $[5]$101:$[10]$101) that compares the years with a year record I have in the filter table.

I checked for blank spaces within the strings, but they all have the same structure, perhaps there is something I'm missing?

Best regards,


Best Answer

  • Carson Penticuff
    Carson Penticuff ✭✭✭✭✭✭
    Answer ✓

    I believe you will need to include the "search type" in your MATCH functions.


    =INDEX($[5]$102:$[10]$125, MATCH("Delayed" + [24]251, $[2]$102:$[2]$125, 0), MATCH($[23]$251, $[5]$101:$[10]$101, 0))


Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!