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?
Can anyone help me with this?
Attachments
-
8.9 KB Views: 7
= ISNUMBER(SEARCH(code, sourceData))
Thank you very much for the reply.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
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)))
Thanks a lot. You are a brilliant.A number of presentation strategies are possible.
As an aside, the padding with spaces can be useful to prevent a code being wrongly matched to part of a longer text string.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)))