Flag Cell if Partial Text is Contained on Another Sheet

MRL

We have a column of text of names and companies on Sheets 1-10 that I want to compare to another Master Sheet that contains only the names. If a cell in Sheets 1-10 contains a name that is on Master Sheet, I want the row flagged. I'm using a helper column in the numerical sheets, and I can get it to work if it's an exact match of names. But if the numerical sheet has any additional text, it will not flag the row.

For example, if the name John Smith is on Master Sheet, and someone enters John Smith/Apple on Sheet 1, I want that row flagged on Sheet 1.

I've found partial success with these formulas on the Sheet 1 helper column:

=IF(CONTAINS([Name]@row, {Master Sheet range}), "Yes", "No")

=IF(COUNTIF({Master Sheet range}, [Name]@row) = "1", "true")

But they only work for exact matches. They only work if you enter John Smith on Sheet 1. If I enter John Smith/Apple, it returns no match.

Does anyone know how I can get it to identify partial matches between the two sheets?

Nathan Slatton

Would a Vlookup with true at the end work?

MRL

https://community.smartsheet.com/discussion/comment/482507#Comment_482507

Thank you for the suggestion. I just tried a Vlookup, and it's pulling in the closest match reference. If I input "Test" in the Name column on Sheet 1, it pulled in "Tess" from my Master Sheet.

Here's what I used:

=VLOOKUP([Name]@row, {Master Sheet}, 1, true)

Any suggestions to tweak that?

Nathan Slatton

@MRL I didnt think of that. ya, Vlookup wouldnt work unless they are all unique identifiers. Have you tried the contains if it is on the same sheet not a cross sheet refrence?

MRL

https://community.smartsheet.com/discussion/comment/482577#Comment_482577

We want to have the master list on one sheet that we can continually keep updated, while dozens of production sheets are checking that and flagging a match.

Nathan Slatton

@MRL Right just testing if the cross reference is checking on cell or the entire column. If you can get it to work on the same sheet you could use a cell/column link as a helper column to reference instead of a cross reference (This idea wouldnt work with data shuttle)

MRL

Ah, I got it - I'll try that approach. So far contains isn't working for me on the same sheet, either.

And thank you!