Cross-sheets sweep check
Hi, I'm trying to cross check if a any of the values (text) from Sheet B 'Value list' column is contained in the row (text string) under analysis in Sheet A 'Description', if found, I want to retrieve the corresponding row vale from Sheet B "Class" column and bring it to Sheet A 'Class' column. I can't figure out the best way to achieve this. Thought about nested IFs, index/match combination, but haven't succeed to make it work.
To consider, searched value can be in any place within the evaluated text string. All values available in 'Value list' should be searched.
Thanks in advance for your help!
@Genevieve P. @Paul Newcome any thoughts? May be you have already tackled an issue like this before?
Answers
-
Try something like this =JOIN(COLLECT({Class}, {Value list}, CONTAINS(@cell, [Description]@row)), ","). Does that work?
-
Hello Adam, thanks for your prompt support. I have just tried it and formula is working well.
Despite it needs a small adjustment, just noticed sometimes I can have the searched word in CAPS and/or LowCase throughout the description string. Is there a way to make the formula CAPS sensitive? Or better said to collect only if there is an exact match (word + format)?
Thanks for your help!
-
The only function I know of that is case-sensitive is FIND, maybe you could try that instead of CONTAINS. You could also use UPPER or LOWER to convert something to all upper or lower case, which may help you as well.
-
Hello @Adam Murphy . I've been trying to implement the FIND function instead of CONTAINS but so far I haven't been able to make it work properly.
I tested a workaround adjusting the searched words format what brings me closer to the solution I want, the drawback is that this searched word adjustment is mostly manual, so there could be a miss at some point.
-
@Juancarlos MORALES Give this a try:
=JOIN(COLLECT({Class}, {Value list}, FIND([Description]@row, @cell) > 0), ",")
-
Hi Paul, thanks for your suggestion. I've just tried it out but the results is not as expected.
I tried the formula just as you post it, but it brings back a BLANK result (I believe it is because the 'search_for' and 'search_in' are inverted in the formula).
When I swap this to items to make the formula as:
=JOIN(COLLECT({Class}, {Value list}, FIND(@cell, [Description]@row)>0,",")
I get a result full of "," as follows: ,,,,,,,,, (like if the search is matching "spaces" and not the words in the list.
What are your thoughts on this?
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.4K Get Help
- 424 Global Discussions
- 221 Industry Talk
- 465 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 145 Just for fun
- 62 Community Job Board
- 463 Show & Tell
- 32 Member Spotlight
- 1 SmartStories
- 301 Events
- 39 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!