INDEX/MATCH formula broken after number passed 100

emcangus

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

Paul Newcome

Ok. So the auto-number is outputting text strings that just look like numbers and the manual entry is being stored as numbers. You have two different data types. Try inserting helper columns to convert everything into text values and then reference them in your formula.

=[Column Name]@row + "" (<-- plus quote quote converts it into a text value)

Paul Newcome

How is the entry number on both sheets populated? I see one is an auto-number type. What about the other sheet?

emcangus

The Entry number is first generated when a person complete's a form and it is entered into the "Entry #" column. Then that assigned entry # is manually entered into another form that populates the COI Entry # and Subaward Entry# columns (mentioned in the formula) in 2 other tracking sheets. The Index Match takes the assigned entry # and if it shows up in one of the two other sheets, fills in that number in the the "Complete" column in the picture above. If the "Complete" column is filled in with the entry # then I know that the process is complete. Hopefully that helps explain my process

Paul Newcome

Ok. So the auto-number is outputting text strings that just look like numbers and the manual entry is being stored as numbers. You have two different data types. Try inserting helper columns to convert everything into text values and then reference them in your formula.

=[Column Name]@row + "" (<-- plus quote quote converts it into a text value)

emcangus

That worked perfectly!

Paul Newcome

Happy to help. 👍️