Return TRUE if 'part text' found in cell range

[AnnieM]

Hi
I would appreciate your help on creating the correct formula. The formula has to include a range across each row (for each student). The formula has to search for specific text contained in the classnames, and then return the result along the same row.

Below is an example, which I hope saves OK in the thread. If not I'll upload an Excel file:

A B C D Results:-
1 class1 class2 class3 class4 Gg Hi Fr Sp
2 10m/Gg1 10m/Hi2 10m/Fr1 10m/En1 TRUE TRUE TRUE FALSE
3 10n/Hi1 10n/En2 10n/Sp1 10n/Ma1 FALSE TRUE FALSE TRUE

Thanks
AnnieM

[AnnieM]

Pls find Excel file.

[Pepe Le Mokko]

Does the attached help?

[Andrew-R]

In cell M2 use the formula:

=NOT(ISNA(MATCH(FALSE,INDEX(ISERROR(FIND(M$1,$A2:$K2)),0),0)))

And copy across and down.

[Andrew-R]

I'd go with Pepe's answer, seeing as they've done it the simple, sensible way

[AnnieM]

Hi Pepe, Thanks for your help and prompt response. This solves the query.
Just out of interest, if I wanted to return "Y or N", or "1 or 0", what additional syntax should be included in the formula?

[AnnieM]

Hi Andrew. Thanks for your solution, which I have also tried and it works well. However, I'll have to spend time deciphering the formula, as there is a lot of nesting going on there! Any chance of breaking it down?
Regards
AnnieM

[Pepe Le Mokko]

For 1 or 0 enter

Please Login or Register  to view this content.

in M2 and pull down and across
Y or N

Please Login or Register  to view this content.

[AnnieM]

Pepe. Fantastic. I will use these formulae again and again. Thanks for your help. AnnieM

[Pepe Le Mokko]

You're welcome

[Andrew-R]

I'm happy to break down my formula if it will help you ...

=NOT(ISNA(MATCH(FALSE,INDEX(ISERROR(FIND(M$1,$A2:$K2)),0),0)))

FIND returns the position of a text string within another string (e.g. =FIND("l","Hello") would return 3, because the first "l" is at position 3 within the string. So in my formula the FIND looks for the characters from the header column (M1) in each cell in the range A2:K2.

=NOT(ISNA(MATCH(FALSE,INDEX(ISERROR(FIND(M$1,$A2:$K2)),0),0)))

If FIND can't find the string you specify then it returns an error (e.g. =FIND("l","Goodbye") returns a #VALUE! error). The ISERROR function checks for this, so for each cell in the range A2:K2 the result will either be FALSE (if the cell contains the string from the header and, therefore, doesn't not return an error) or TRUE (if the string isn't found and the FIND function returns an error.

=NOT(ISNA(MATCH(FALSE,INDEX(ISERROR(FIND(M$1,$A2:$K2)),0),0)))

Because the FIND statement is evaluating multiple cells it will return an array of answers, rather than a single one. The INDEX function just packages up this array into a form that's slightly easier to work with.

=NOT(ISNA(MATCH(FALSE,INDEX(ISERROR(FIND(M$1,$A2:$K2)),0),0)))

As we now have an array which looks something like {TRUE,TRUE,TRUE,FALSE,TRUE,TRUE,TRUE,TRUE,TRUE,TRUE,TRUE}, where each TRUE represents an error (and, therefore, the value we're looking for not being found) the MATCH statement tries to match a value of FALSE in that array. If there are any FALSE values then it means that the header value we were looking for has been found.


=NOT(ISNA(MATCH(FALSE,INDEX(ISERROR(FIND(M$1,$A2:$K2)),0),0)))

If there are no FALSE values in the array MATCH will return a #N/A error and ISNA checks for that. In effect it means that if we haven't found any matches for the header text then the formula up to this point will return TRUE, and if there are any matches it will return FALSE.

=NOT(ISNA(MATCH(FALSE,INDEX(ISERROR(FIND(M$1,$A2:$K2)),0),0)))

Because that is the exact opposite of the values you wanted to return we use NOT to turn TRUE into FALSE, and vice-versa.

See, I told you Pepe's formula was a lot simpler

[AnnieM]

Wow! I'll have a play with the formula tonight. Thanks for taking the time to explain it!

[Haseeb Avarakkan]

You could also use,

=ISNUMBER(MATCH("*"&M$1&"*",A2:K2,0))+0

[exiul]

Thank you Pepe Le Mokko and Andrew-R! I have a similar issue and your posts helped so much!
I have connected question, if instead of false/true, y/n, 1/0, I want to return complete cell that contained the value I was looking for (for example return 10A/Hi1 because it contains Hi), how could I do it?
Many thanks in advance!

[Fotis1991]

Unfortunately your post does not comply with Rule 2 of our Forum RULES. Do not post a question in the thread of another member -- start your own thread.

If you feel an existing thread is particularly relevant to your need, provide a link to the other thread in your new thread.

Old threads are often only monitored by the original participants. New threads not only open you up to all possible participants again, they typically get faster response, too.