This article includes instructions on how to match addresses from the FCC’s Broadband Serviceable Location Fabric to your address list.
Note: There is a secondary address file in the same ZIP file as the Fabric data that you received from Cost Quest. In cases where a location_id has multiple addresses associated with it, according to CostQuest’s data sources, these additional addresses are listed in this file and could be helpful with address matching.
Tip: Save a copy before trying to match data. It might be easier having addresses in a separate file from other information.
TEXTJOIN Function
This function combines the text from multiple ranges and/or strings and includes a specified delimiter between each text value.
1. In the Fabric List, insert a column left of location_id to combine the address columns for more accurate matching while ignoring blank cells.
a. Type =TEXTJOIN(delimiter, ignore_empty, text1, [text2], …)
b. Combine & and TEXTJOIN to get the correct formatting
Example: =TEXTJOIN(", ", TRUE, C2:E2)&" "&TEXTJOIN("-",TRUE,F2:G2)
Combining Data with the Ampersand (&) Symbol
This function is used if you have formatted your address list in a single cell with commas between the addresses.
- In the Fabric List, insert a column left of location_id to combine the address columns for more accurate matching.
- Type = and select the first cell you want to combine.
- Type & and use quotation marks with an enclosed space, commas, and dash (“, “) (“-”).
Example: =C2&", "&D2&", "&E2&" "&F2&"-"&G2
4. Drag or double-click the square in the bottom right corner to fill in the formula for all rows.
5. Remove spaces if your data mismatch.
CONCAT Function
This function combines text from multiple ranges and/or strings.
- In the Fabric List, insert a column left of location_id to combine the address columns.
- Type =CONCAT( and then select the cells you want to combine.
=CONCAT(text1, [text2],…)
3. Drag or double-click the square in the bottom right corner of the cell to fill in the formula for all rows.
Example: =CONCAT(C2:G2) or =CONCAT(C2,D2,E2,F2,G2)
4. Use CONCAT on your list of addresses as well to get the most matches.
5. For easier matching, you can also remove spaces from the CONCAT column.
a. Copy and paste values only in the CONCAT column.
b. Highlight the CONCAT column.
c. Find/Replace (Ctrl+F).
d. In the Replace tab, enter a space (“ ”) in Find what and leave Replace with
e. Click Replace All.
Note: You can use this to remove other formatting, like commas.
VLOOKUP Function
This function is used to find things either in a table or in a range by row – such as your address dataset within the Concat column or a combination of the above functions.
1. Use the formula below in the first cell of the column where you want the location_id.
=VLOOKUP (what you want to look up, where you want to look for it, column number in the range containing the value to return, return an Approximate or Exact match, indicated as 1/TRUE or 0/FALSE)
2. Drag or double-click the square in the bottom right corner of the cell to fill in the formula for all rows.
Example: =VLOOKUP(G2,fabric!A:B,2,FALSE)
Note: Assuming unique address and formatting matching the Fabric.
Fabric Dataset
Addresses to Match
VLOOKUP Errors
If any addresses do not have an exact match they will return #N/A.
Use a combination of methods above and below to match the data. If you don’t have an exact match, you can separate errors to find a fuzzy match.
Example: In the Addresses example, Street is spelled out, but in the Fabric, it is abbreviated. This is not an exact match, so an #N/A is returned for VLOOKUP.
Addresses to Match
Fabric Dataset
Excel Fuzzy Lookup
This function performs fuzzy matching of textual data. You can fuzzy-join similar rows between two different tables. The matching is robust for a wide variety of errors including spelling mistakes, abbreviations, synonyms, and added/missing data.
- Install Fuzzy Lookup in Excel:
a. Fuzzy Lookup Add-In –
https://www.microsoft.com/en-us/download/details.aspx?id=15011
b. After downloading the installation file, open it and install following the instructions.
2. Prepare data:
a. Format your data into tables.
Note: Both the Fabric and your address list must be in the same Excel workbook.
b. Select the cell range.
c. Click Insert tab.
d. Choose Table.
e. Clearly name the table.
Select the table > Table Design tab > Table Name > enter name.
3. Create Fuzzy Lookup:
a. Open a new tab or select a location where you want your matches to go.
b. Click the Fuzzy Lookup tab.
c. Choose Fuzzy Lookup.
d. Select the two tables you are using.
e. Highlight columns from each to match.
f. Add them to the Match Columns.
g. Select/unselect your Output Columns
h. Click Go.
4. Check your results:
Similarity will be the last column in the new matched table.
With 1.0 as a perfect match, the VLOOKUP error shows a match with 0.8796 similarity.
Click Configure to change the threshold of your matches.