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]

Fantastic, thanks again Peter!

Glad it helped again.

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)?

Would this be sufficient?
For this function, the arguments are:
- the entire range that includes the column to be looked up and the column that holds the results to be returned.
- the cell containing the word(s) to be looked up.
- the column within the first argument range that holds the values to be looked in.
- the column within the first argument range that holds the values to be returned.
- the particular match (1st, 2nd, 3rd etc) to be used.

Function CheckWords(rng As Range, lookupwords As String, lookupcol As Long, resultcol As Long, 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.Columns(lookupcol).Cells
    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 = Intersect(c.EntireRow, rng.Columns(resultcol)).Value
      Exit For
    End If
  Next c
End Function

Two examples of the function use. Each formula shown is copied down.

Excel Workbook
	A	B	C	D	E	F	G
1				List		Lookup	book
2		ABC1		Did you read any book Makerson wrote?			ABC1
3		ABC2		He is a home-maker			ABC4
4		ABC3		I filled in the logbook			ABC7
5		ABC4		Ask the maker of the book
6		ABC5		Have you seen a boobook owl?
7		ABC6		Tom Makerson was at the booklaunch
8		ABC7		Did you read the book Makerson wrote?
9
10						Lookup	the book
11				He is a home-maker	Result 1		Result 3
12				I filled in the logbook	Result 2		Result 7
13				Ask the maker of the book	Result 3
14				Did you read any book Makerson wrote?	Result 4
15				Have you seen a boobook owl?	Result 5
16				Tom Makerson was at the booklaunch	Result 6
17				Did you read the book Makerson wrote?	Result 7
Sheet3

 

[MichaelS94]

Would this be sufficient?
For this function, the arguments are:
- the entire range that includes the column to be looked up and the column that holds the results to be returned.
- the cell containing the word(s) to be looked up.
- the column within the first argument range that holds the values to be looked in.
- the column within the first argument range that holds the values to be returned.
- the particular match (1st, 2nd, 3rd etc) to be used.

Once again works perfectly, thanks Peter - you've been so helpful!

 

[Peter_SSs]

Once again works perfectly, thanks Peter - you've been so helpful!

 

[loadedlongboard]

Hi Peter / All,

I know this is an old thread, but so far has matched my needs perfectly for a project I'm working on. The only caveat being that the speed of the CheckWords function is too slow for me to use it as a complete solution.

I wanted to reach out to see if there is a way to speed up the lookup code for a large dataset? The source data has 300,100 Rows with some duplicates, so I need to run this formula in 3 columns to pull out the multiple values for each duplicate across its row...(I believe I found a way to tweak your original formula to get the results along rows vs. down columns).

With the size of my source data, utilizing the CheckWords function for even a small number of new entries to match (50) is causing processing times of about 1-1/2 to 2 minutes. There are multiple other formulas that rely on the result of the CheckWords result as well, which further makes the spreadsheet slow.

I know its a lot of data to parse through, but is there a way to change the vba code to allow for a more efficient lookup? I researched a bit and it sounds like I can use a variable array, which stores the lookup data in memory vs. re-looking it up for each entry, but I couldn't figure out how to convert your code to that type of model. I'm also not sure how much time something like this would save, but I thought it was worth a shot to ask

Thanks for any and all help on this.

 

[Peter_SSs]

Welcome to the MrExcel board - at least for posting!

I researched a bit and it sounds like I can use a variable array, which stores the lookup data in memory vs. re-looking it up for each entry, ..

1. That could well be an option, but before making any suggestions, could you post small set of dummy data and the expected results. My signature block below has a link for ways to do that in a format we can see the layout and also copy/paste the data to test with.

2. Do you need this to be done by a function or could we just have some code that delivered the (static) result(s) and was not immediately responsive to any changes in the underlying data?

 

[loadedlongboard]

1. Below is a sample of the data I’m working with. The “Source Data” is a list of about 301,000 addresses. I have modified them so that all suffixes, directions, etc. are listed in all forms (E/ East, Ter/Terrace, etc). The matching TaxIDs are in column B.

a) The 2nd table is a Lookup Table. The goal is to have the correct TaxID auto populate when I enter just the “Street, Direction (if any), and Zip”. There are some duplicate addresses, and I am looking to pull the first 3 matching TaxIDs.

b) I have to Lookup each address twice, once with the Direction Suffix, and once without. Sometimes the data from external sources adds a Dir Suffix and the Source Data doesn’t have it, which results in an error on the Lookup (Row 3 is an example).


2. Function vs. Static - I'm not familiar with the static code option you mentioned...but in terms of purpose, the only thing needed is that when a new Address/ZIP is added, the TAXID Searches will automatically fill down and calculate. If the static code can achieve this more quickly, then that would be great. Below is how the data will be used:

1) I will get daily feeds with roughly 30-50 Addresses/ZIPs, and will paste that into TABLE2 to identify the TaxID. Many other formulas occur as well, but the TaxID is the first.
2) I may need to manually enter an Address/ZIP to get the same information, but time to calculate will be important especially in those instances....

** The Source Data (301,000 Addresses) will not change in terms of order or quantity. The # of rows will stay the same. Not sure if that helps or not **

Thank you for your response to my initial inquiry. Since starting to work with these large datasets I've been trying to find faster ways to accomplish basic calculations. I'm not yet at a point where I feel comfortable utilizing VBA Macros for all my data, but from these forums it sounds like those types of solutions are required to speed up the process. Thanks again for any and all assistance.


SOURCE DATA

	A	B
1	COMBINED ADDRESS	TAXID
2	7708 MARWOOD DR Drive Drive DR 20735	17090897504
3	2417 UNIVERSITY BLVD Boulevard Boulevard BLVD 20783	17171893023
4	10400 WOODLAWN BLVD Boulevard Boulevard BLVD 20774	17131466242
5	14218 HIDDEN FOREST DR Drive Drive DR 20607	17053875432
6	14218 HIDDEN FOREST DR Drive Drive DR 20607	17055586403

<tbody>
</tbody>

TABLE 2

	A	B	C	D	E	F	G	H	I
1	STREET ADDRESS	DIR	ZIP	ADDRESS - W/ DIR	ADDRESS - NO DIR	TAXID - W/ DIR	TAXID - NO DIR	TAXID #2	TAXID #3
2	7708 MARWOOD DR		20735	7708 MARWOOD DR 20735	7708 MARWOOD DR 20735	17090897504
3	2417 UNIVERSITY BLVD	E	20783	2417 UNIVERSITY BLVD E 20783	2417 UNIVERSITY BLVD 20783		17171893023
4	10400 WOODLAWN BLVD		20774	10400 WOODLAWN BLVD 20774	10400 WOODLAWN BLVD 20774	17131466242
5	14218 HIDDEN FOREST DRIVE		20607	14218 HIDDEN FOREST DRIVE 20607	14218 HIDDEN FOREST DRIVE 20607	17053875432		17055586403

<tbody>
</tbody>

Worksheet Formulas Cell Formula D2 =TRIM(CONCATENATE(A2," ",B2," ",C2)) E2 =TRIM(CONCATENATE(A2," ",C2)) F2 =CheckWords(Sheet1!$A$2:$A$300929,D2,ROWS(F2:F2),1) G2 =IF(F2="",CheckWords(Sheet1!$A$2:$A$300929,E2,ROWS(G2:G2),1),"") H2 =IF(F2<>"",CheckWords(Sheet1!$A$2:$A$300929,D2,ROWS($1:$2),1),CheckWords(Sheet1!$A$2:$A$300929,E2,ROWS($1:$2),1)) I2 =IF(F2<>"",CheckWords(Sheet1!$A$2:$A$300929,D2,ROWS($1:$3),1),CheckWords(Sheet1!$A$2:$A$300929,E2,ROWS($1:$3),1)) <tbody> </tbody>

<tbody>
</tbody>

 

[Peter_SSs]

1. Does the Source Data table ever have the 'Direction' in it? If so, could we see another set of sample data and expected results (no need to display any formulas this time*) at least some of which have the direction in the source data?

2. In the Results table, do you actually need either of the address columns or have you just done those to try to get to the final results in the TAXID columns?

3. BTW, I noticed your Test posts & wondering what HTML Maker you settled on as I still think there may still be some "issues" with what you are posting?

 

[loadedlongboard]

1. Yes, sometimes. The data isn't uniformly assembled, so the Direction will be in the front at times (5507 N Park Ave), at the end (5507 Park Ave N), or sometimes in the middle (5507 Park North Ave). It also randomly switches between the abbreviated form (N) to the long form (North). It happens often that for whatever reason The Source/County Data does not have a DIR Suffix, while another Data Source (Sale Records) does have one for the same property. It also goes the other way, where "5507 N Park Ave" is a distinct address from "5507 Park Ave". That's why I need to have two Lookup Strings - Address w/ Direction and Address - No Direction.

Example 1: In Table 2, Rows 4 & 5 have the same address - 2034 Anvil Lane - however Row 4 has a "South" Direction suffix. The Source Data has a unique TaxID for 1) "2034 N ANVIL LANE 20748" and 2) "2034 S ANVIL LANE 20748". Since Row 4 has the "S" DIR Suffix, only 1 TaxID is matched. However Row 5, w/out a DIR Suffix, finds a match for both the North and South Addresses in the Source Data.

Example 2: In Table 2, Row 3, the Address searched was "2417 UNIVERSITY BLVD E 20783". But the Source Data does not have a Direction Suffix (2417 UNIVERSITY BLVD Boulevard 20783). There was no match when using the DIR Suffix Address (Cell G2), but found a match when removing the DIR Suffix (Cell H2).

This is why I liked your CheckWords example, because the order of each word in the Lookup String does not matter, and catches all potential matching TAXIDs.
--If my Source Data does not include a DIR Suffix, but the Lookup Address data does, the 2nd Address LookUp (Column E) will still find its Match.
--If the Source Data does include a DIR Suffix, but the Lookup Address does not, then the worst case is I will have multiple matches (i.e. N & S Address matches), and would need to flag/review those. I'm not aware of a way to get around that issue though.


2. I added those two columns to deal with the issues discussed above re: the DIR Suffix. If there's a way to search by both Address Lookup String types within the solution itself, then no I would not need those columns.

3. I'm using the "MrExcel HTML Maker 08072017". But I think (hopefully) I've figured it out....Let me know otherwise please.


<b></b><table cellpadding="2.5px" rules="all" style=";background-color: rgb(255,255,255);border: 1px solid;border-collapse: collapse; border-color: rgb(187,187,187)"><colgroup><col width="25px" style="background-color: rgb(218,231,245)" /><col /><col /></colgroup><thead><tr style=" background-color: rgb(218,231,245);text-align: center;color: rgb(22,17,32)"><th></th><th>A</th><th>B</th></tr></thead><tbody><tr ><td style="color: rgb(22,17,32);text-align: center;">1</td><td style="font-weight: bold;text-align: center;;">COMBINED ADDRESS</td><td style="font-weight: bold;text-align: center;;">TAXID</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">2</td><td style=";">2010 NORTH N ANVIL LANE  20748</td><td style=";">17121336544</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">3</td><td style=";">2417 UNIVERSITY BLVD Boulevard Boulevard BLVD 20783</td><td style=";">17171893023</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">4</td><td style=";">2034 S SOUTH ANVIL LN LANE 20748</td><td style=";">17121337674</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">5</td><td style=";">2034 NORTH N ANVIL LANE 20748</td><td style=";">17121336429</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">6</td><td style=";">11258 W WEST WESTPORT DR Drive 20720</td><td style=";">17131480847</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">7</td><td style=";">6119 SW SOUTHWEST CRAIN HWY Highway 20772</td><td style=";">17030200493</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">8</td><td style=";">6119 SW SOUTHWEST CRAIN HWY Highway 20772</td><td style=";">17030200892</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">9</td><td style=";">16800 VILLAGE DR Drive 20772</td><td style=";">17030207647</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">10</td><td style=";">5300 W WEST MAPLE SHADE LN LANE 20772</td><td style=";">17151746445</td></tr></tbody></table><p style="width:4.8em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid rgb(187,187,187);border-top:none;text-align: center;background-color: rgb(218,231,245);color: rgb(22,17,32)">Sheet1


<b></b><table cellpadding="2.5px" rules="all" style=";background-color: rgb(255,255,255);border: 1px solid;border-collapse: collapse; border-color: rgb(187,187,187)"><colgroup><col width="25px" style="background-color: rgb(218,231,245)" /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: rgb(218,231,245);text-align: center;color: rgb(22,17,32)"><th></th><th>A</th><th>B</th><th>C</th><th>D</th><th>E</th><th>F</th><th>G</th><th>H</th><th>I</th><th>J</th></tr></thead><tbody><tr ><td style="color: rgb(22,17,32);text-align: center;">1</td><td style="font-weight: bold;text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FFFFFF;;">STREET</td><td style="font-weight: bold;text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FFFFFF;;">DIR</td><td style="font-weight: bold;text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FFFFFF;;">CITY</td><td style="font-weight: bold;text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FFFFFF;;">ZIP</td><td style="font-weight: bold;text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FFFFFF;;">ADDRESS - W/ DIR</td><td style="font-weight: bold;text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FFFFFF;;">ADDRESS - NO DIR</td><td style="font-weight: bold;text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FFFFFF;;">TAXID - W/ DIR</td><td style="font-weight: bold;text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FFFFFF;;">TAXID - NO DIR</td><td style="font-weight: bold;text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FFFFFF;;">TAXID #2</td><td style="font-weight: bold;text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FFFFFF;;">TAXID #3</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">2</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FFFFFF;;">2010 NORTH ANVIL LANE</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FFFFFF;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FFFFFF;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FFFFFF;;">20748</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FFFFFF;;">2010 NORTH ANVIL LANE 20748</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FFFFFF;;">2010 NORTH ANVIL LANE 20748</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FFFFFF;;">17121336544</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FFFFFF;;"></td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FFFFFF;;"></td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FFFFFF;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">3</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FFFFFF;;">2417 UNIVERSITY BLVD</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FFFFFF;;">E</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FFFFFF;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FFFFFF;;">20783</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FFFFFF;;">2417 UNIVERSITY BLVD E 20783</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FFFFFF;;">2417 UNIVERSITY BLVD 20783</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FFFFFF;;"></td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FFFFFF;;">17171893023</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FFFFFF;;"></td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FFFFFF;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">4</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FFFFFF;;">2034 ANVIL LANE</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FFFFFF;;">S</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FFFFFF;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FFFFFF;;">20748</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FFFFFF;;">2034 ANVIL LANE S 20748</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FFFFFF;;">2034 ANVIL LANE 20748</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FFFFFF;;">17121337674</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FFFFFF;;"></td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FFFFFF;;"></td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FFFFFF;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">5</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FFFFFF;;">2034 ANVIL LANE</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FFFFFF;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FFFFFF;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FFFFFF;;">20748</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FFFFFF;;">2034 ANVIL LANE 20748</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FFFFFF;;">2034 ANVIL LANE 20748</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FFFFFF;;">17121337674</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FFFFFF;;"></td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FFFFFF;;">17121336429</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FFFFFF;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">6</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FFFFFF;;">11258 WESTPORT DR</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FFFFFF;;">W</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FFFFFF;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FFFFFF;;">20720</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FFFFFF;;">11258 WESTPORT DR W 20720</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FFFFFF;;">11258 WESTPORT DR 20720</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FFFFFF;;">17131480847</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FFFFFF;;"></td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FFFFFF;;"></td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FFFFFF;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">7</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FFFFFF;;">6119 SW CRAIN HWY</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FFFFFF;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FFFFFF;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FFFFFF;;">20772</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FFFFFF;;">6119 SW CRAIN HWY 20772</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FFFFFF;;">6119 SW CRAIN HWY 20772</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FFFFFF;;">17030200493</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FFFFFF;;"></td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FFFFFF;;">17030200892</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FFFFFF;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">8</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FFFFFF;;">16800 VILLAGE DR</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FFFFFF;;">S</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FFFFFF;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FFFFFF;;">20772</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FFFFFF;;">16800 VILLAGE DR S 20772</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FFFFFF;;">16800 VILLAGE DR 20772</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FFFFFF;;"></td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FFFFFF;;">17030207647</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FFFFFF;;"></td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FFFFFF;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">9</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FFFFFF;;">5300 MAPLESHADE LN</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FFFFFF;;">W</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FFFFFF;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FFFFFF;;">20772</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FFFFFF;;">5300 MAPLESHADE LN W 20772</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FFFFFF;;">5300 MAPLESHADE LN 20772</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FFFFFF;;"></td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FFFFFF;;"></td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FFFFFF;;"></td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FFFFFF;;"></td></tr></tbody></table><p style="width:4.8em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid rgb(187,187,187);border-top:none;text-align: center;background-color: rgb(218,231,245);color: rgb(22,17,32)">Sheet3</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: rgb(255,255,255)" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: rgb(255,255,255);border-collapse: collapse; border-color: rgb(187,187,187)"><thead><tr style=" background-color: rgb(218,231,245);color: rgb(22,17,32)"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">E2</th><td style="text-align:left">=TRIM(<font color="Blue">CONCATENATE(<font color="Red">A2," ",B2," ",D2</font>)</font>)</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">F2</th><td style="text-align:left">=TRIM(<font color="Blue">CONCATENATE(<font color="Red">A2," ",D2</font>)</font>)</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">G2</th><td style="text-align:left">=CheckWords(<font color="Blue">Sheet1!$A$2:$A$10,E2,ROWS(<font color="Red">G2:G2</font>),1</font>)</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">H2</th><td style="text-align:left">=IF(<font color="Blue">G2="",CheckWords(<font color="Red">Sheet1!$A$2:$A$10,F2,ROWS(<font color="Green">H2:H2</font>),1</font>),""</font>)</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">I2</th><td style="text-align:left">=IF(<font color="Blue">G2<>"",CheckWords(<font color="Red">Sheet1!$A$2:$A$10,E2,ROWS(<font color="Green">$1:$2</font>),1</font>),CheckWords(<font color="Red">Sheet1!$A$2:$A$10,F2,ROWS(<font color="Green">$1:$2</font>),1</font>)</font>)</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">J2</th><td style="text-align:left">=IF(<font color="Blue">G2<>"",CheckWords(<font color="Red">Sheet1!$A$2:$A$10,E2,ROWS(<font color="Green">$1:$3</font>),1</font>),CheckWords(<font color="Red">Sheet1!$A$2:$A$10,F2,ROWS(<font color="Green">$1:$3</font>),1</font>)</font>)</td></tr></tbody></table></td></tr></table><br />

 

[Peter_SSs]

So far I have not been able to come up with something that I think would be significantly faster and also deal efficiently with the direction/no direction issue.

I can see some possible problems with your present solution too. For example if the Source table contained the address
20774 SMITH DR 20607
and the other sheet contained
20607 SMITH DR 20774
then the existing CheckWords function will call those a match when it shouldn't.

If something does occur to me that might work for what you want then I will certainly come back to the thread.

 

[loadedlongboard]

Hi Peter, I may have come up with a way to make the lookup faster, but am having a problem with a portion of it.

To reduce the process time of the CheckWords function, I'm attempting to reduce the "Range" size in the formula by sorting the Source Address Data numerically, and finding the 1st & Last Row position of the rows that match the Lookup Addresses' "Street #" (Columns E & F). This way, the Checkwords formula is using a much smaller Range when calculating each result versus using the entire 300K+ rows in the Source Data for each lookup.

To utilize the row values as references, the "INDIRECT" function works with "CheckWords" (Column G), but slows down the entire sheet by recalculating anytime a cell is changed. Below is a sample of my Checkwords formula utilizing INDIRECT:

A	B	C	D	E	F	G
TAXID	SOURCE DATA - ADDRESS	LOOKUP ADDRESS	STREET # MATCH	1ST ROW #	LAST ROW #	TAXID MATCH 1 - FORMULA
17212348860	1 Austin Ct Court 20740	14619 April Street 20607	14619	254598	254605	=CHECKWORDS(INDIRECT("B" &E2 & ":B" &F2), C2, ROWS(G2:G2),-1)
17131485424	1 Barberry Ct Court 20774	6003 Mustang Place 20737	6003	122753	122869	=CHECKWORDS(INDIRECT("B" &E3 & ":B" &F3), C3, ROWS(G3:G3),-1)

<tbody>
</tbody>

To get around this, I added the following two modules. This function is supposed to be static, and will only update when forcibly recalculated....thereby preventing the delay that INDIRECT causes from its constant revaluations:

PublicFunction INDIRECTVBA(ref_text As String)
INDIRECTVBA = Range(ref_text)
EndFunction

PublicSub FullCalc()
Application.CalculateFull
EndSub

However, when I replace "INDIRECT" with "INDIRECTVBA" within the CheckWords formula, I get a #Value! error. In separate cells, INDIRECT and INDIRECTVBA return the same array of values, just in a different format. I.E: using Cell's E2 and F2 in both formulas returns the following:

=INDIRECT("B" &E2 & ":B" &F2): #Value! Error; Pressing F9 reveals the matching 8 addresses in the format ={"Address 1";"Address 2";"Address 8"}

=INDIRECTVBA("B" &E2 & ":B" &F2): Shows the 1st Address within the specified Row Range; Pressing F9 reveals the same 8 addresses & same format as INDIRECT...={"Address 1";"Address 2";"Address 8"}

Book1
	C	D	E	F	G	H	I
1	LOOKUP ADDRESS	1ST ROW MATCH	LAST ROW MATCH	INDIRECT	INDIRECTVBA	INDIRECT - F9	INDIRECTVBA - F9
2	14619 April STREET 20607	254599	254606	#VALUE!	14619 APRIL ST Street Street ST 20607	14619 APRIL ST Street Street ST 20607	14619 APRIL ST Street Street ST 20607
3	6003 Mustang PLACE 20737	122754	122870	#VALUE!	6003 10TH PL Place Place PL 20782	6003 10TH PL Place Place PL 20782	6003 10TH PL Place Place PL 20782
Sheet9

Cell Formulas
Range	Formula
F2	=INDIRECT("B"&D2&":B"&E2)
G2	=INDIRECTvba("B"&D2&":B"&E2)
H2	={"14619 APRIL ST Street Street ST 20607";"14619 ARABIAN LN LANE LANE LN 20715";"14619 ARGOS PL Place Place PL 20774";"14619 CAMBRIDGE CIR Circle Circle CIR 20707";"14619 HAMPSHIRE HALL CT Court Court CT 20772 110";"14619 HAWLEY LN LANE LANE LN 20774";"14619 LONDON LN LANE LANE LN 20715";"14619 TURNER WOOTTON PKWY Parkway Parkway PKWY 20774"}
I2	={"14619 APRIL ST Street Street ST 20607";"14619 ARABIAN LN LANE LANE LN 20715";"14619 ARGOS PL Place Place PL 20774";"14619 CAMBRIDGE CIR Circle Circle CIR 20707";"14619 HAMPSHIRE HALL CT Court Court CT 20772 110";"14619 HAWLEY LN LANE LANE LN 20774";"14619 LONDON LN LANE LANE LN 20715";"14619 TURNER WOOTTON PKWY Parkway Parkway PKWY 20774"}

I have no idea why INDIRECT works while INDIRECTVBA does not. Is the array result from INDIRECTVBA somehow not compatible with the Checkwords formula? Is there a way to remedy this? Or a different solution for the same issue?
----Limiting the the Row # references speeds up the Checkwords formula a great deal, but the delay from the INDIRECT function is going to be an issue later on in the worksheet.

Thanks again, and sorry for the long post. I've been looking up hundreds of forum posts and can't find 1) a reason why it doesn't work and 2) how to fix it. Thank you for helping me in my VBA learning journey.