"Contains" Cross-Reference Formula

Art Schneiderheinze

I have two Sheets, one with a list of full course master names (e.g., ABS-435-BL-6W-1D-Master), and another sheet with a list of course sections (e.g., ABA 435 BL 6W).

Is there a way to use CONTAINS in a cross-reference formula to check all of the course master names in Sheet 1 to see if any contain the cell value in Sheet 2?

So, for example, if the cell value in Sheet 2 is ABA-435-BL-6W, it would look in Sheet 1 to see if there is a course master that contains that value. It would find ABS-435-BL-6W-1D-Master, since it contains the string, "ABA-435-BL-6W". I want it to report back to Sheet 2 the name of the master (ABS-435-BL-6W-1D-Master) that contains that string. If none of the masters contain the string, it says "No Master".

I thought I could use the INDEX-MATCH logic, but instead of MATCH, I could use CONTAINS. I am also looking at one column , rather than an array, as I do when I typically use INDEX-MATCH.

SHEET 1

[CELL MASTERS]

ABS-415-BL-6W-1D-Master

ABS-415-OL-6W-Master

ABS-435-BL-6W-1D-Master

ABS-435-BL-10W-1D-Master

ABS-435-OL-6W-Master

ABS-455-BL-6W-1D-Master

ABS-455-OL-6W-Master

SHEET 2

[FIND MASTER]

String to look for "ABS-435- BL-6W"

Looks in Sheet 1 for a course master that CONTAINS that string

Reports back "ABS-435-BL-6W-1D-Master".

If it finds nothing that contains the string, reports back "No Master"

Genevieve P.

Hi @aschneiderheinze74206

You're absolutely correct to be going down the COLLECT and CONTAINS route, but I would suggest using JOIN(COLLECT in case multiple cells contain what you're looking for.

Try this:

=JOIN(COLLECT({Sheet One Column}, {Sheet One Column}, CONTAINS("ABS-435- BL-6W", @cell)), " / ")

Notice how I've used the {Sheet One Column} range twice... once to show the values I want returned, and once to show the range I'm looking in for the criteria. I've also used " / " as my delimiter so that if there are multiple cells that contain this string they will be easily distinguishable... but you can change this to " , " or some other indicator that there's a new value.

Now, this will return a blank cell if there's no match... if you want it to say "No Master", we can use an IF statement to say, IF this formula returns a blank cell, then say "No Master". Otherwise, give us the result of the formula.

Full formula:

=IF(JOIN(COLLECT({Sheet One Column}, {Sheet One Column}, CONTAINS("ABS-435- BL-6W", @cell)), " / ") = "", "No Master", JOIN(COLLECT({Sheet One Column}, {Sheet One Column}, CONTAINS("ABS-435- BL-6W", @cell)), " / "))

Let me know if this works for you!

Cheers,

Genevieve

Genevieve P.

Hi @aschneiderheinze74206

Great question! Yes, it is possible to do this, and you're absolutely correct, we would just need to add in an AND function:

=JOIN(COLLECT({Sheet One Column}, {Sheet One Column}, AND(CONTAINS("SPY-525-BL-10W", @cell), CONTAINS([SSBSECT_CAMP_CODE]@row, @cell))), " / ")

Just don't forget to add that extra closing ) before the comma and the " / "

Now lets add that into your full formula with the IF statement:

=IF(JOIN(COLLECT({Sheet One Column}, {Sheet One Column}, AND(CONTAINS("SPY-525-BL-10W", @cell), CONTAINS([SSBSECT_CAMP_CODE]@row, @cell))), " / ") = "", "No Master", JOIN(COLLECT({Sheet One Column}, {Sheet One Column}, AND(CONTAINS("SPY-525-BL-10W", @cell), CONTAINS([SSBSECT_CAMP_CODE]@row, @cell))), " / "))

🙂

Genevieve P.

Hi @aschneiderheinze74206

You're absolutely correct to be going down the COLLECT and CONTAINS route, but I would suggest using JOIN(COLLECT in case multiple cells contain what you're looking for.

Try this:

=JOIN(COLLECT({Sheet One Column}, {Sheet One Column}, CONTAINS("ABS-435- BL-6W", @cell)), " / ")

Notice how I've used the {Sheet One Column} range twice... once to show the values I want returned, and once to show the range I'm looking in for the criteria. I've also used " / " as my delimiter so that if there are multiple cells that contain this string they will be easily distinguishable... but you can change this to " , " or some other indicator that there's a new value.

Now, this will return a blank cell if there's no match... if you want it to say "No Master", we can use an IF statement to say, IF this formula returns a blank cell, then say "No Master". Otherwise, give us the result of the formula.

Full formula:

=IF(JOIN(COLLECT({Sheet One Column}, {Sheet One Column}, CONTAINS("ABS-435- BL-6W", @cell)), " / ") = "", "No Master", JOIN(COLLECT({Sheet One Column}, {Sheet One Column}, CONTAINS("ABS-435- BL-6W", @cell)), " / "))

Let me know if this works for you!

Cheers,

Genevieve

Art Schneiderheinze

Worked perfectly!!! Thanks

Art Schneiderheinze

OK, found a few one-offs that were problematic.

SHEET 1

[CELL MASTERS]

SPY-525-BL-10W-1D-CH-Master

SPY-525-BL-10W-1D-TA-Master

SHEET 2

[FIND MASTER]

String to look for "SPY-525-BL-10W"

The output of the look up is:

SPY-525-BL-10W-1D-CH-Master / SPY-525-BL-10W-1D-TA-Master

This is correct. However, is there a way to modify the formula, so while also looking for "SPY-525-BL-10W" it also looks for the campus [SSBSECT_CAMP_CODE]?

So, in the row in Sheet 2, where [SSBSECT_CAMP_CODE]@row = "TA", the formula looks for "SPY-525-BL-10W" but also looks for "TA"? The output would then just be SPY-525-BL-10W-1D-TA-Master.

The part that is unknown in Sheet 2 is the "1D" part, otherwise I'd include the [SSBSECT_CAMP_CODE]@row in the search. It can be different values, depending on the term.

This probably isn't possible but something like AND(contains "SPY-525-BL-10W", contains [SSBSECT_CAMP_CODE]@row)

Genevieve P.

Hi @aschneiderheinze74206

Great question! Yes, it is possible to do this, and you're absolutely correct, we would just need to add in an AND function:

=JOIN(COLLECT({Sheet One Column}, {Sheet One Column}, AND(CONTAINS("SPY-525-BL-10W", @cell), CONTAINS([SSBSECT_CAMP_CODE]@row, @cell))), " / ")

Just don't forget to add that extra closing ) before the comma and the " / "

Now lets add that into your full formula with the IF statement:

=IF(JOIN(COLLECT({Sheet One Column}, {Sheet One Column}, AND(CONTAINS("SPY-525-BL-10W", @cell), CONTAINS([SSBSECT_CAMP_CODE]@row, @cell))), " / ") = "", "No Master", JOIN(COLLECT({Sheet One Column}, {Sheet One Column}, AND(CONTAINS("SPY-525-BL-10W", @cell), CONTAINS([SSBSECT_CAMP_CODE]@row, @cell))), " / "))

🙂