Cross-sheets sweep check

Juancarlos MORALES

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?

Adam Murphy

Try something like this =JOIN(COLLECT({Class}, {Value list}, CONTAINS(@cell, [Description]@row)), ","). Does that work?

Juancarlos MORALES

@Adam Murphy

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!

Adam Murphy

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.

Juancarlos MORALES

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.

Paul Newcome

@Juancarlos MORALES Give this a try:

=JOIN(COLLECT({Class}, {Value list}, FIND([Description]@row, @cell) > 0), ",")

Juancarlos MORALES

@Paul Newcome

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?