How to list of cell values that contain or equal to given data.

[arif120]

I want to get the list of data where cell value contain or equal to given data (lookup value).
Can anyone help me with this?

 

[Peter Bartholomew]

If a list of TRUE/FALSE will do, you could use

= ISNUMBER(SEARCH(code, sourceData))

If required, the returned values could be used to filter the source data to show only matches.

 

[bosco_yip]

A similar idea as compare to Peter's solution,

Just add space " " in front of And behind the Entry code and the Source data,

Which, consider if the Entry code put in front of (as per image C8) or behind the Source data (as per image C7),

something like this.

In B3, formula copied down :

=ISNUMBER(SEARCH(" "&A$2&" "," "&C3&" "))

 

[arif120]

A similar idea as compare to Peter's solution,

Just add space " " in front of And behind the Entry code and the Source data,

Which, consider if the Entry code put in front of (as per image C8) or behind the Source data (as per image C7),

something like this.

In B3, formula copied down :

=ISNUMBER(SEARCH(" "&A$2&" "," "&C3&" "))

View attachment 80309

Thank you very much for the reply.
Basically I wanted output with non blank match value.

 

[Peter Bartholomew]

A number of presentation strategies are possible.

Condition
= ISNUMBER(SEARCH(code, sourceData))

Showing matches within adjacent column
= IF(ISNUMBER(SEARCH(code, sourceData)), sourceData)

Showing matches as a filtered list
= FILTER( sourceData, ISNUMBER(SEARCH(code, sourceData))

Reducing to {0,1} for display using number format symbols
= N(ISNUMBER(SEARCH(code, sourceData)))

As an aside, the padding with spaces can be useful to prevent a code being wrongly matched to part of a longer text string.

 

[arif120]

View attachment 80311

A number of presentation strategies are possible.

Condition
= ISNUMBER(SEARCH(code, sourceData))

Showing matches within adjacent column
= IF(ISNUMBER(SEARCH(code, sourceData)), sourceData)

Showing matches as a filtered list
= FILTER( sourceData, ISNUMBER(SEARCH(code, sourceData))

Reducing to {0,1} for display using number format symbols
= N(ISNUMBER(SEARCH(code, sourceData)))

As an aside, the padding with spaces can be useful to prevent a code being wrongly matched to part of a longer text string.

Thanks a lot. You are a brilliant.
That's exactly what I wanted. Problem solved.