Search for keywords in another sheet

[madhav_kulkarni]

Sheet2 contains more than 200000 rows (with unique strings in Column A and B). I would like to search 1 to 3 keywords from Sheet1, Keywords in B1,B2,B3 and get all matching cells from Column A from Sheet2 to Sheet1, starting with Cells A5. For Column B (B5 onwards), I am using vlookup to get matching column B for each of Column A values.

Following formula does the job but is too slow as the processor keeps counting and the process starts immediately after 1st keyword is entered.. entering 2nd and/or 3rd keyword takes long time... and then retrieval takes even longer time

=IF($B$1<>"",IFERROR(INDEX(Sheet2!$A$2:$B$260169, SMALL(IF(ISERROR(SEARCH($B$1, Sheet2!$A$2:$A$260169&Sheet2!$B$2:$B$260169)*SEARCH($B$2, Sheet2!$A$2:$A$260169&Sheet2!$B$2:$B$260169)*SEARCH($B$3, Sheet2!$A$2:$A$260169&Sheet2!$B$2:$B$260169)),"", ROW(A2:A260169)-MIN(ROW(A2:A260169))+1), ROW(A1)), COLUMN(A1)),""),"")

A macro to quickly look for 1-3 keywords from Sheet1 in Column A of Sheet2 and then getting matching values from Column A and B to Sheet1 would be of great help.

 

[madhav_kulkarni]

Thanks Peter. I will try. I may have 1 or 2 or 3 keywords to search for. I guess your code will still work as other cells would be empty and "nothing" would be searched in combination with keyword 1 or keyword 1 and 2. but if you think the code will not work if 1 or 2 of the 3 keyword cell is blank, please help me with improved code. Thanks again.

 

[madhav_kulkarni]

Thanks Peter again. Amazing stuff! That code works with 1 or 2 or 3 keywords. The only glitch is that when the keywords do not match there is runtime error 1004 at which asks for debugging at "With .Range("A5:B5").Resize(k)"
so there needs some message rather than code saying that the "keywords combination is not found"..
Is that doable?

 

[Peter_SSs]

Is that doable?

Yes, just add in the blue code where shown

  Next i
  If k > 0 Then
    Application.ScreenUpdating = False
    With Sheets("Sheet1")
      .UsedRange.Resize(, 2).Offset(4).Clear
      With .Range("A5:B5").Resize(k)
        .Value = c
        .Columns.AutoFit
      End With
    End With
    Application.ScreenUpdating = True
  Else
    MsgBox "keywords combination is not found"
  End If
End Sub

 

[madhav_kulkarni]

Amazing stuff! Peter. That worked. I owe you a coffee, at least.