Idex/Match is refusing to match against any "Project ID" that does not start with "SM"

PSExcellence

I cannot for the life of me figure out why an Index/Match between a project Metadata Sheet and the project Intake Sheet refuses to find any matches unless the Project ID has "SM" in it. This was originally the "Project Management Office" template from Smartsheet, but has since been revised.

My formula on the Metadata sheet is:

=INDEX({Project Intake- Project Name}, MATCH(([Project ID]@row), {Project Intake- Project ID}))

Where {Project Intake- Project Name} is referencing the Project Name column on the intake, and {Project Intake- Project ID} is referencing the Project ID column on the intake. It doesn't return any matches unless the Project ID starts with "SM".

Here are screenshots of the two columns on the Intake Sheet, my formula on the Metadata sheet, and showing a match found when "SM" is in the Project ID, and no match found when it is not.

We already have one department using SM in their numbering convention, and we don't want duplicate Project IDs between departments. What on earth is causing this??

Sarah Keortge

Try defining the search type as not sorted (0) in your MATCH formula

=INDEX({Project Intake- Project Name}, MATCH(([Project ID]@row), {Project Intake- Project ID},0))