INDEX/MATCH formula broken after number passed 100

I have been using an INDEX/MATCH formula to identify entries from one sheet to another. I used an automatic numbering system starting at 000 to index/match. If there is no match, then the formula returns a blank cell. Entry # 100 was deleted out of the sheet due to an error and after that the INDEX/MATCH is no longer working. It matches the entries from 000-099, but is not matching them after 101. I converted the formula to a column formula, so nothing has changed in the formula from entry 099-101 Any ideas how to fix this?

Formula I'm using: =IF([Agency Type]@row = "MAHEC Internal", IFERROR(INDEX({COI Entry #}, MATCH([Entry #]@row, {COI Entry #}, 0)), " "), IF([Agency Type]@row = "Subrecipient External", IFERROR(INDEX({Subaward Entry#}, (MATCH([Entry #]@row, {Subaward Entry#}, 0))), " ")))

The final column to the right is the column that should be returning the index/match number

Best Answer


Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!