• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

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

  • List cell contain or equal to.xlsx
    8.9 KB · Views: 7
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.
 
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&" "))

80309
 
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

  • IMG_20220802_070310.jpg
    IMG_20220802_070310.jpg
    125.4 KB · Views: 4
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.
 
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.
 
Back
Top