CODEWITHSUNDEEP
excel-formula
HotSauceCoconuts
HotSauceCoconuts

Reputation: 321

How to find partial number match in whole column?

I need to find if a number in A2, lets say 123, can be found in any row of Column B. This number might be repeated e.g. 123,123 or it might be combined with text or another number e.g. 123,456 or RF939,123

I have found this formula that works to find if the number is present for a single row:

=LEN(A2)-LEN(SUBSTITUTE(A2,B2,"",1))>0

And I have found this formula that works to find a match for a partial string in a column:

=MATCH("*"&A2&"*",$B:$B,0)>0

I have yet to find one that will work for numbers or text/number strings.

Possibly the SUBSTITUTE method could be expanded to work with an array, but I am not too savvy with array forumlas.

Is this possible or will I have to use VBA?

Upvotes: 0

Views: 80

Answers (1)

Scott Craner
Scott Craner

Reputation: 152525

Use AGGREGATE:

=AGGREGATE(15,7,ROW($B$1:$B$100)/(ISNUMBER(SEARCH(","&A2&",",","&$B$1:$B$100&","))),1)

Or if you do not care about the , deliniating the matches so 123 will be found in 51234 then

=AGGREGATE(15,7,ROW($B$1:$B$100)/(ISNUMBER(SEARCH(A2,$B$1:$B$100))),1)

Upvotes: 1

Related Questions