What if you have a list of string and you want to compile a list of them contain certain word. The feature is very common when we search for certain file in windows explorer through Search Option.
Lets examine the following list:
My name is faseeh
My name is not faseeh
My Name is faraz
My Name is fawwad
My Name is farooq
Each of the them contains "My Name", two of them contains "faseeh", all five contains "is". Now the question is that how will we find the string contains "Faseeh"?
The following formula does the trick, Enter and Press Control+Shift+Enter, and drag down.
=IFERROR(INDEX($A$1:$A$5,SMALL(IF(IFERROR(SEARCH($B$1,$A$1:$A$5,1)>=1,0),ROW($A$1:$A$5)),ROW(C1)),0),"---")
The Search Function:
IF(IFERROR(SEARCH($B$1,$A$1:$A$5,1)>=1,0)
The Search function looks up for the Lookup_Criteria, which is present in B1 (i.e Faseeh) in the Array A1:A5, if the search is gives VALUE# Error, the IFERROR() formula replaces the error with zero. This in retrun is feed to the IF() that return ROW() # for the Trues.
=IFERROR(INDEX($A$1:$A$5,SMALL({1,2,FALSE,FALSE,FALSE,ROW(C1)),0),"---")
The SMALL() function returns the first smallest value in the array, the result is feed to the second argument of INDEX() function that is a row number, with column offset equals to zero, if the result is an error the outside IFERROR() formula wraps it into and gives you "---".
Hope this post helps you once again.
No comments:
Post a Comment