Expanding =IF(ISNUMBER(SEARCH

sru · #1 03-09-2018, 05:37 AM

Hi,

I am currently working with 2 sets of data, both data sensitive so I can not share examples, which I need to match.
The novice in me thought "easy there must be an =if contains", which turns out to be =IF(ISNUMBER(SEARCH) but that's fine.

When using this formula it works well if the data in column a1 contains any data from b1 - and I can get it to just produce that data that is matched, leaving the rest. This exactly what I want, apart from it only ever works when the data is in the same row!

How do I get the formula to show data that appears in any row of column b is contained in any row of column a

COLUMN A
A1
1A
3BA
ACC1
4ACC

COLUMN B
1
2
3
4
5

Any help with this is greatly appreciated people!!

NBVC · #2 03-09-2018, 06:16 AM

What are your expected results for this sample?

sru · #3 03-09-2018, 06:32 AM

Hi,

I'm hoping to amend the formula so I can search for any number found in column b, i.e 12345, appears in any part of a cell in column a, i.e acc12345.

I can only get this to work if the values are on the same row, not anywhere in either column.

=IF(ISNUMBER(SEARCH("*"&K:K&"*",$A$2:$A$33)),K:K," No")

Is what I am currently using, but this will only return a result if the value in "K" appears on the same row in "A".

NBVC · #4 03-09-2018, 06:59 AM

Try:

=IFERROR(LOOKUP(2,1/SEARCH($B$1:$B$5,A1),$B$1:$B$5),"No")

copied down after adjusting range to suit. Don't use whole columns, use only range containing data.

ArviLaanemets · #5 03-09-2018, 07:27 AM

Create a function (you have to change from *.xlsx to *.xlsm)

Code:

Public Function FoundInRange(parRange As Range, parValue As Variant) As Boolean
 
    Dim intRows As Integer
    intRows = parRange.Rows.Count
 
    Dim IsFound As Boolean
    Dim i As Integer
    IsFound = False
 
    For i = 1 To intRows
        If InStr(parRange(i), CStr(parValue)) > 0 Then
            IsFound = True
            Exit For
        End If
    Next i
 
    FoundInRange = IsFound
End Function

Now, into your table p.e. on row 2 enter the formula like

Code:

=FoundInRange($A$2:$A$6,B2)

, and copy it down.

sru · #6 03-09-2018, 07:27 AM

Try:

=IFERROR(LOOKUP(2,1/SEARCH($B$1:$B$5,A1),$B$1:$B$5),"No")

copied down after adjusting range to suit. Don't use whole columns, use only range containing data.

Brilliant, this worked perfectly.

Any chance you can break it down so I know why it worked ha

Thanks very much for the help!

NBVC · #7 03-09-2018, 07:50 AM

If you use the "Evaluate Formula" tool in the Formulas tab, you can step through to see what's happening.

The Search() part does what you expect and is seaching for any of the B1:B5 entries in A1. The 1/ part converts any entries to values of 1 or less and leaves errors as errors.

The Lookup(2.. part looks for the last time that a value of 2 or less is found in the result array, and it then aligns it to the location with B1:B5 to return the corresponding result.

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
ISNUMBER formula not working	bishweshwar	Excel	3	03-13-2017 10:07 AM
Expanding a formula bug on Mac's Excel	Bouss	Excel	0	01-27-2017 06:22 PM
Prevent cell from expanding vertically.	snowboarder2	Word Tables	1	08-24-2016 02:52 PM
IF, ISNUMBER & AND function	Dkline	Excel	3	06-04-2014 06:40 AM
How to prevent Tables from expanding	HappyFingers	Word	1	11-29-2010 01:54 PM