CODEWITHSUNDEEP
excelexcel-formula
William
William

Reputation: 141

complex search and find in excel

I have 2 columns in excel.

Column A has a list of 10,000 rows of data. Column B has 500 rows of text to find in column A

I know I can use formulas like: =IF(SUM(IFERROR(FIND(TRIM($C$2:$C$3023),A2),0))>0,"Yes","No") But this only searches for 1 cell search at a time.

Is there any way I can create a function that searches for any text/numbers found in column C2:C500 and find any of that data in A2:A10000?

Here's a basic image of what i'm trying to achieve.

enter image description here

Upvotes: 0

Views: 509

Answers (2)

user3514930
user3514930

Reputation: 1717

You can use also a simple formula:

=IF(IFERROR(VLOOKUP(A2;$C$2:$C$9;1;);0)=0;"No";"Yes")

instead or your...

Upvotes: 1

Gary's Student
Gary's Student

Reputation: 96753

Enter the following UDF (User Defined Function) in a standard module:

Public Function IsItThere(rBig As Range, rLittle As Range) As String
    Dim v As String
    IsItThere = "Not Found"
    v = rBig.Value
    For Each r In rLittle
        v2 = r.Value
        If v2 <> "" Then
            If InStr(v, v2) > 0 Then
                IsItThere = "found"
                Exit Function
            End If
        End If
    Next r
End Function

To use it, in B2 enter the formula:

=IsItThere(A2,C$2:C$500)

and copy down.

User Defined Functions (UDFs) are very easy to install and use:

  1. ALT-F11 brings up the VBE window
  2. ALT-I ALT-M opens a fresh module
  3. paste the stuff in and close the VBE window

If you save the workbook, the UDF will be saved with it. If you are using a version of Excel later then 2003, you must save the file as .xlsm rather than .xlsx

To remove the UDF:

  1. bring up the VBE window as above
  2. clear the code out
  3. close the VBE window

To use the UDF from Excel:

=IsItThere(A2,C$2:C$500)

To learn more about macros in general, see:

http://www.mvps.org/dmcritchie/excel/getstarted.htm

and

http://msdn.microsoft.com/en-us/library/ee814735(v=office.14).aspx

and

http://www.cpearson.com/excel/WritingFunctionsInVBA.aspx

for specifics on UDFs

Macros must be enabled for this to work!

Upvotes: 1

Related Questions