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

#### arif120

##### New Member
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?

#### Attachments

• 8.9 KB Views: 7

#### Peter Bartholomew

##### Well-Known Member
If a list of TRUE/FALSE will do, you could use
Code:
``= ISNUMBER(SEARCH(code, sourceData))``
If required, the returned values could be used to filter the source data to show only matches.

#### bosco_yip

##### Excel Ninja
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

##### New Member
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.

#### Attachments

• 125.4 KB Views: 4

#### Peter Bartholomew

##### Well-Known Member
A number of presentation strategies are possible.
Code:
``````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

##### New Member
View attachment 80311
A number of presentation strategies are possible.
Code:
``````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.