Index Match not working


Hello, Smart community!

I have something that seems VERY simple that just doesn't want to work. I'm using Index/Match.

The sheet I'm referencing has a Primary Column (QMS) with a numbering system to identify if it is a Risk (R) or Opportunity (O). I have put in 17 rows of "R"s (i.e. R01, R02, etc).

The sheet I'm trying to use Index/Match has the formula:

=INDEX({Title1}, MATCH([Project ID]@row, {QMS})

where I want it to return the title where the R or O number match. It has worked fine as long as the number is R## (R01, R02), but not if it is an O## (O01). With O##, I get a #NO MATCH error. I have checked the my O and 0 are correct. I even changed it to a R18 (since it worked 17 times before) and it works. The number is a Primary column, so there are not any properties that I can find (all Text/Number) with no restrictions.

Any ideas.



Best Answer


Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!