Lookup string (with wildcard) from list and return multiple values

[MichaelS94]

Hi,

I'm trying to use the index and small functions (from a formula i found online) to search a list of text and return any values that have a partial match.

E.g. I search for "book" and it returns a corresponding number for "antique books", "assembling of books", "book publishing" etc.

The formula i used is below and i found it online - it doen'st work with wiildcards and only works if there is an exact match.

=IF(ISERROR(INDEX($A$1:$B$15600,SMALL(IF($A$1:$A$15600=$E$1,ROW($A$1:$A$15600)),ROW(1:1)),2)),"",INDEX($A$1:$B$15600,SMALL(IF($A$1:$A156008=$E$1,ROW($A$1:$A$15600)),ROW(1:1)),2))

Here is what my spreadsheet looks like and ideally how I want it to work:

List	Number	Lookup	Book
bla bla	1	Matches:	4
bla bla	2		7
bla bla	3		11
book maker	4
bla bla	5
bla bla	6
antique books	7
bla bla	8
bla bla	9
bla bla	10
book publisher	11
bla bla	12
bla bla	13
bla bla	14
bla bla	15

<tbody>
</tbody>

Could any of you please have a look and offer any recommendations /advice? Essentially I just have a long list and want to be able to search for something and it would return any (often multiple) suitable matches.

Thanks so much - really stuck on this one!

 

[Peter_SSs]

here is one way, using a user-defined function. To implement ..

1. Right click the sheet name tab and choose "View Code".

2. In the Visual Basic window use the menu to Insert|Module

3. Copy and Paste the code below into the main right hand pane that opens at step 2.

4. Close the Visual Basic window.

5. Enter the formula as shown in the screen shot below and copy down.

6. If using Excel 2007 or later your workbook will need to be saved as a macro-enabled workbook (*.xlsm)

Function CheckWords(rng As Range, lookupwords As String, Optional num As Long = 1) As String
  Dim lookup() As String
  Dim i As Long, Ctr As Long
  Dim c As Range
  Dim bPassed As Boolean
  
  lookup = Split(lookupwords)
  For Each c In rng
    bPassed = True
    For i = 0 To UBound(lookup)
      If InStr(1, " " & c.Value & " ", " " & lookup(i) & " ", vbTextCompare) = 0 Then
        bPassed = False
        Exit For
      End If
    Next i
    If bPassed Then Ctr = Ctr + 1
    If Ctr = num Then
      CheckWords = c.Value
      Exit For
    End If
  Next c
End Function

Excel Workbook
	A	B	C	D
1	List	Number	Lookup	book maker
2	I read a book	1		Ask the book maker
3	He is a home-maker	2		Ask the maker of the book
4	Ask the book maker	3
5	Ask the maker of the book	4
6	Have you seen a boobook owl?	5
7	Tom Makerson was at the booklaunch	6
8	Did you read the book Makerson wrote?	7
9
search words

 

[MichaelS94]

Spreadsheet Formulas

Cell Formula D2 =CheckWords(A$2:A$8,D$1,ROWS(D$2:D2)) <tbody> </tbody>

<tbody>
</tbody>

Excel tables to the web >> Excel Jeanie HTML 4

That's amazing, works better than I could ever have imagined!

One thing - to my understaning, UDFs would not work if I shared the workbook with others (am I right?). Is there a way around this, or would I need to get everyone who uses the workbook to import/add the CheckWords UDF first?

 

[Peter_SSs]

That's amazing, works better than I could ever have imagined!

Happy days.

One thing - to my understaning, UDFs would not work if I shared the workbook with others (am I right?). Is there a way around this, or would I need to get everyone who uses the workbook to import/add the CheckWords UDF first?

The UDF should be saved in the workbook itself & I think that is what you have already done. The workbook can be Shared and the UDF should still work for all users.

When you first share the workbook you will receive a message noting that the workbook contains macros and that macros "cannot be viewed or edited in shared workbooks". That message does not say they cannot be "used" in shared workbooks.

There are two issues to consider:

1. If the udf (or any other macros) in the workbook need to be reviewed or edited, you simply need to ensure all except one user has saved & closed the workbook, unshare it, view/edit the UDF and save & re-share the workbook.

2. I have to use shared workbooks a reasonable amount at my workplace & rarely have problems, but many others apparently have a different experience. You might consider doing the following Google search for a few discussions in this forum about shared workbook issues "problem shared workbook site:MrExcel.com"

 

[MichaelS94]

Function CheckWords(rng As Range, lookupwords As String, Optional num As Long = 1) As String
  Dim lookup() As String
  Dim i As Long, Ctr As Long
  Dim c As Range
  Dim bPassed As Boolean
  
  lookup = Split(lookupwords)
  For Each c In rng
    bPassed = True
    For i = 0 To UBound(lookup)
      If InStr(1, " " & c.Value & " ", " " & lookup(i) & " ", vbTextCompare) = 0 Then
        bPassed = False
        Exit For
      End If
    Next i
    If bPassed Then Ctr = Ctr + 1
    If Ctr = num Then
      CheckWords = c.Value
      Exit For
    End If
  Next c
End Function

Peter - this has been working really well, thanks. However, there would be one small modification I would love to make and it would be great to get some help.

I would quite like to include an almost wildcard-esque ability, so that if I searched "book" it would return "books" or "bookmaker" etc. I don't want it to include results which end in book (e.g. *book), just results which contain it at the start (e.g. book*).

Would this be possible/an easy modification of the code?

Thanks!

 

[Peter_SSs]

Try just making this change

<del>If InStr(1, " " & c.Value & " ", " " & lookup(i) & " ", vbTextCompare) = 0 Then</del>
If InStr(1, " " & c.Value, " " & lookup(i), vbTextCompare) = 0 Then

 

[MichaelS94]

Perfect, thank you again Peter!

Just to check my understanding of the code and improve my learning, if I wanted to get it to return *book (e.g. cookbook) , I would use the following code:

If InStr(1,c.Value & " ",lookup(i) & " ", vbTextCompare) = 0 Then

Is that correct?

 

[Peter_SSs]

Perfect, thank you again Peter!

Just to check my understanding of the code and improve my learning, if I wanted to get it to return *book (e.g. cookbook) , I would use the following code:

If InStr(1,c.Value & " ",lookup(i) & " ", vbTextCompare) = 0 Then

Is that correct?

At a quick glance that looks right, but the easiest way to confirm is for you to try it.

 

[MichaelS94]

Function CheckWords(rng As Range, lookupwords As String, Optional num As Long = 1) As String
  Dim lookup() As String
  Dim i As Long, Ctr As Long
  Dim c As Range
  Dim bPassed As Boolean
  
  lookup = Split(lookupwords)
  For Each c In rng
    bPassed = True
    For i = 0 To UBound(lookup)
      If InStr(1, " " & c.Value & " ", " " & lookup(i) & " ", vbTextCompare) = 0 Then
        bPassed = False
        Exit For
      End If
    Next i
    If bPassed Then Ctr = Ctr + 1
    If Ctr = num Then
      CheckWords = c.Value
      Exit For
    End If
  Next c
End Function

Peter - this has been working really well, thanks. However, there would be one small modification I would love to make and it would be great to get some help.

I would quite like to include an almost wildcard-esque ability, so that if I searched "book" it would return "books" or "bookmaker" etc. I don't want it to include results which end in book (e.g. *book), just results which contain it at the start (e.g. book*).

Would this be possible/an easy modification of the code?

Thanks!

Hi Peter,

Sorry to dig this one up again! I'm using the code for a slightly different purpose and could do with your help.

Is there a way to change the UDF so that it returns the value of a different cell. E.g. you search for "xyz", it looks in a given range, locates "xyz" in let's say cell C3, and rather than returning the value in C3, returns the value in A3? So the same row, but first column?

Don't know if possible, and I can't quite work out if/how I can change the code to enable that.

Thanks!

 

[Peter_SSs]

Try changing these two lines in the existing function by adding the blue text.

Function CheckWords(rng As Range, lookupwords As String, Optional num As Long = 1, Optional ColOffset As Long) As String


CheckWords = c.Offset(, ColOffset).Value

Then when using the function, add the 4th argument when needed. So, looking in column C but wanting the result from column A, you would use something like this

=CheckWords(C$2:C$8,F$1,ROWS(F$2:F2),-2)

To still use the function like before, leave the 4th argument out altogether, or set it to 0

 

[MichaelS94]

Function CheckWords(rng As Range, lookupwords As String, Optional num As Long = 1, Optional ColOffset As Long) As String


CheckWords = c.Offset(, ColOffset).Value

Fantastic, thanks again Peter!

I don't think it's possible with offset, but could you use an absolute 'offset' to the first column (Column A) rather than a relative offset (e.g. -2 columns to left)?