Index/Match/Contains formula not working in all cases

twarner

I have a question regarding an Index/Match formula.

This is for a cross-sheet function.

I have two sheets: Call them "Project" and "Jeopardy"

Project sheet columns:

PID: this is a 6 or 7 digit number identifying the Project

Path to Green: this is the "Path to Green" for a Jeopardy against the project. This is where the formula needs to live to bring in the "PTG" data in the Jeopardy sheet.

Jeopardy sheet columns:

PID: This is for the same 6 or 7 digit project identifying number. HOWEVER, it might contain multiple PID #'s separated by a "," - like above.

The challenge is to lookup the PTG data from the Jeopardy sheet if the PID from the Jeopardy sheet CONTAINS the PID from the Project sheet.

The ISSUE is that I have the formula working in the case of multiple PIDs now, but the standard case of a single PID doesn't seem to work.

Here's what I have so far: (this is the formula for the Project sheet in the Path to Green column:

=INDEX(COLLECT({PID Sheet - PTG}, {PID Sheet - PID}, CONTAINS(PID@row, @cell)), 1)

This formula is based on one provided by @Art Schneiderheinze in a different/related answer and it works beautifully for what it was intended for. But I need the formula to also work for the single PID case.

Thanks for your help.

Tony

Paul Newcome

The problem is that your source PID column contains two different data types (numbers and text strings).

Insert a helper column on both sheets (can be hidden after setup to keep the sheet looking clean) and use this:

=PID@row + ""

This will convert everything into text strings so that you have the same data type throughout. You would then use these helper columns in your formula.

Paul Newcome

The problem is that your source PID column contains two different data types (numbers and text strings).

Insert a helper column on both sheets (can be hidden after setup to keep the sheet looking clean) and use this:

=PID@row + ""

This will convert everything into text strings so that you have the same data type throughout. You would then use these helper columns in your formula.

twarner

@Paul Newcome - that is brilliant! I totally didn't see that. That totally explains why the "410497, 410498" works (it's treating it as text) and the 123456 does not (it's treating it as a number. Hence the #Invalid Value error.

Thank you so much!

Tony

Paul Newcome

Happy to help. 👍️