"Contains" Cross-Reference Formula
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"
Best Answers
-
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
-
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))), " / "))
🙂
Answers
-
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
-
Worked perfectly!!! Thanks
-
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)
-
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))), " / "))
🙂
Help Article Resources
Categories
Check out the Formula Handbook template!
