Crossing multiple data bases with a Partial Text/Number match

Fernando Ced · 05/10/22

Hello all! This is my first question.

I am building a tracker called "My Project Tracker" that takes information from multiple "master sheets" built by different teams with different purposes. See screenshot for example.

"My Project Tracker" has the "Project ID" as user input, then it populates the "Project Name" using the "Data Pool" sheet. Now, I want to pull the "Project Risk" to my tracker from "Team X Tracker".

The problem is that some projects on the "Team X tracker" are missing the "Project ID" or have a typo, and sometimes the "Project Name" is registered slightly different from the "Data Pool" Sheet. I want to build a formula that populates the "Project Risk" Column on "My Project Tracker" by doing this:

Compare the "Project ID" input on "My Project Tracker" to "Project ID" on "Team X Tracker", if there is a match, populate "Project Risk"
IFERROR, compare "Project Name" on "My Project Tracker" to "Project Name" on "Team X Tracker", if there is a match, populate "Project Risk"
IFERROR, if "Project ID on "Team X Tracker" CONTAINS LEFT([Project ID]@row, 3]), print "Check for possible match"
IFERROR, if "Project Name" on "Team X Tracker" CONTAINS LEFT([Project Name]@row, 3]), print "Check for possible match"

Is something like this possible? Is there a way to evaluate the % of probability to have a match between two sheets?

Thanks in advance!

Genevieve P. · 05/12/22

Hi @Fernando Ced

Yes, we can build a formula with exactly those four instructions.

1. Compare the "Project ID" input on "My Project Tracker" to "Project ID" on "Team X Tracker", if there is a match, populate "Project Risk"

Since you already are bringing int he Project Name based on the Project ID in the Data Pool sheet, all we need to do here is check if the Project ID in this current sheet has a match in your Team X Tracker. We can do this with an INDEX(MATCH:

INDEX({Project Risk - Tracker}, MATCH([Project ID]@row, {Project ID - Tracker}, 0))

2. IFERROR, compare "Project Name" on "My Project Tracker" to "Project Name" on "Team X Tracker", if there is a match, populate "Project Risk"

Same thing with this one. If there's an error (no match) based on the Project ID, then we can check the Project Name:

INDEX({Project Risk - Tracker}, MATCH([Project Name]@row, {Project Name - Tracker}, 0))

So with the IFERROR, add those two together:

IFERROR(INDEX({Project Risk - Tracker}, MATCH([Project ID]@row, {Project ID - Tracker}, 0)), INDEX({Project Risk - Tracker}, MATCH([Project Name]@row, {Project Name - Tracker}, 0)))

Now, if either of these two creates an error, we can move on to your next instruction:

3. IFERROR, if "Project ID on "Team X Tracker" CONTAINS LEFT([Project ID]@row, 3]), print "Check for possible match"

The way I would do this is to use a COUNTIFS to see if there are any number of rows in the Tracker sheet that contain the left 3 values in the ID column as the left 3 values of that cell.

IF(COUNTIFS({Project ID - Tracker}, CONTAINS(LEFT([Project ID]@row, 3), LEFT(@cell, 3))) > 0, "Check for possible match",

Then if this isn't true, check the Project column for the same thing.

4. IFERROR, if "Project Name" on "Team X Tracker" CONTAINS LEFT([Project Name]@row, 3]), print "Check for possible match"

IF(COUNTIFS({Project Name - Tracker}, CONTAINS(LEFT([Project Name]@row, 3), LEFT(@cell, 3))) > 0, "Check for possible match"

FULL FORMULA:

=IFERROR(IFERROR(INDEX({Project Risk - Tracker}, MATCH([Project ID]@row, {Project ID - Tracker}, 0)), INDEX({Project Risk - Tracker}, MATCH([Project Name]@row, {Project Name - Tracker}, 0))), IF(COUNTIFS({Project ID - Tracker}, CONTAINS(LEFT([Project ID]@row, 3), LEFT(@cell, 3))) > 0, "Check for possible match", IF(COUNTIFS({Project Name - Tracker}, CONTAINS(LEFT([Project Name]@row, 3), LEFT(@cell, 3))) > 0, "Check for possible match")))

Let me know if this works for you!

Cheers,

Genevieve

Genevieve P. · 05/12/22