- Fuzzy Lookup Add-in For Excel Macro
- How Do You Add Fuzzy Lookup In Excel
- Fuzzy Lookup Add-in For Excel Macbook
- How To Use Excel Fuzzy Lookup Add In
- Excel, fuzzy Using Fuzzy Lookup In Excel To Match Inconsistently Spelt Items, like People’s Names. Posted on September 4. Use Microsoft’s Fuzzy Lookup add-in.
- In this easy Hindi tutorial of Microsoft Excel 2016/2013/2010/2007, we will show you the use of Fuzzy Lookup add-in. First, we will download & install the Fu.
Description
The Fuzzy Lookup add-in from Microsoft works only in Excel 2007 - 2013 and can only compare data in two similar datasets formatted as tables. Our add-in works with any version of Excel 2019, 2016 - 2010, supports all Windows versions, and can handle any range of cells no matter its format.
$120 (per person) includes an online webinar for 2 hours of CPE credit
Microsoft Excel provides many tools that can be used to perform many different types of data analysis useful to the audit process. Specifically, the Data Analysis Toolpak can be added to Microsoft Excel and can be used to perform a variety of different analyses including sampling, random number generation, correlation, descriptive statistics, histograms, and regression. Additionally, the Fuzzy Lookup add-in can help auditors find inconsistent (or fuzzy) data increasing the likelihood to find mismatched data.
Participants should have Excel for Microsoft 365, Excel 2019, or Excel 2016 installed on their computer prior to attending this workshop. Workshop instructors will be demonstrating Excel for Windows and will make note of Mac differences when significant.
Learning objectives:
- Install the data analysis toolpak
- Perform analyses including correlation, descriptive statistics, histograms, and regression on data
- Perform sampling and random number generation on data
- Install the fuzzy lookup add-in
- Perform a fuzzy lookup on data
Program Level: Beginning
Field of Study: Auditing
Who Should Attend: Auditors
Prerequisites: none
Instructional Method: Webinar (Group Internet Based)
Advanced Preparation Required: Participants should have Excel for Microsoft 365, Excel 2019, or Excel 2016 installed on their computer prior to attending this workshop.
CPE Credit Hours: 2
Date: Tuesday, July 13, 2021
Time: 1 p.m. – 3 p.m. Central
Microsoft Excel Data Analysis Toolpak: From Beginner to Expert Webinar Leaders:
Wendy Tietz, Professor, Kent State University
Wendy M. Tietz, PhD, CPA, CMA, CSCA, CGMA, is a professor in the Department of Accounting in the College of Business Administration at Kent State University, where she teaches financial and managerial accounting. Dr. Tietz is a Certified Microsoft Trainer and has earned the Microsoft Office Specialist Excel designation. She is a member of the American Accounting Association, IMA, AICPA, and the Ohio Society of CPAs. She has published articles in such journals as Issues in Accounting Education, Journal of Emerging Technologies in Accounting, IMA Educational Case Journal, and others. Dr. Tietz has received several national teaching awards and is the co-author of two accounting textbooks published by Pearson Education.
Tracie Miller-Nobles, Professor, Austin Community College
Tracie L. Miller-Nobles, CPA, is an associate professor at Austin Community College. Prof. Miller-Nobles received her master’s degree in accounting and is working on her doctoral degree in Adult Education from Texas A&M University. She is on the Board of Directors for the American Accounting Association and on the Board of Directors for Teachers of Accounting at Two-Year Colleges. Tracie has received several teaching and professional awards including the AAA J. Michael and Mary Anne Cook Prize, TXCPA Outstanding Accounting Educator, and TXCPA Rising Star. Prof. Miller-Nobles is a co-author of Horngren’s Accounting published by Pearson.
Excel Fuzzy Lookup Add-In is used to match similar, but not exactly matching data. This function is often used instead of VLOOKUP, when we want to compare two columns which have very similar data, but not exactly the same. As an output, Fuzzy Lookup returns a table of matched similar data in the chosen column. In daily data manipulation, there is a common need to compare two same data sets where one of them comes from some external source and can be misspelled or typed incorrectly. In this tutorial, you will see how to install Fuzzy Lookup Add-In, prepare data and create a Fuzzy Lookup, which can be very useful in data consolidation and save a lot of time.
- Installation of Fuzzy Lookup in Excel
- Explanation of the function
- Preparation of data for Fuzzy Lookup
- Create Fuzzy Lookup
Fuzzy Lookup is not a standard Excel function, therefore you can’t find it in your standard tabs and buttons. In order to enable this function, Microsoft created an Add-In which can be downloaded from the following link:
After you have downloaded the installation file, you need to open it and install following instructions. Once you have installed the Add-In, next time when you open an Excel it will automatically import Add-In. As a result, you will get a new tab at the end of a Ribbon called “Fuzzy Lookup” and a button with the same name:
Fuzzy Lookup Add-in For Excel Macro
As mentioned in the intro of the article, Fuzzy Lookup is used when we want to match two sets of data (two tables), but we don’t have exactly the same values in matching fields. For example, we want to match two tables based on values in column “Name” and in a first table we have value “Michael Jackson”, while in a second table we have similar, but misspelled name “Michal Jackson”. In this case, if we use standard VLOOKUP function, it will not match this two values because it’s looking for the exact match. Using of Fuzzy Lookup solves this problem, by matching columns based on their similarity.
This is very often used when we get a table in Excel imported from some other source, or just manually copied and want to match it with another table with the same data sorted out. In most cases, a first table will have many typing mistakes and misspelled words and would first need to be cleaned manually to be able to Lookup it with our prepared table. This can be very time consuming and that’s the point where Fuzzy Lookup saves precious time. Please note that the matching columns must be formatted as text.
Before being able to do a Fuzzy Lookup, we need to format our data into tables. To do this select cells range, click on Insert tab and choose Table. After we have created two tables, they need to be named in order to be used in Fuzzy Lookup function. This is done by selecting the whole table and entering a name into a Name Box:
Now we have data ready for Fuzzy Lookup: As you can see on previous pictures, we have two tables: the first one (named Sales_Actual) contains data on actual sales per salesperson (Columns “Sales Person” and “Sales Actual”) and the second one contains data of target sales per salesperson (Columns “Sales person” and “Sales Target”).
Once we have formatted our data in Spreadsheet, we can start creating Fuzzy Lookup with two tables created. We can imagine that the first table is imported from some other data source and presents a report of sales per sales person, while the second table contains arranged table created in our Excel file which contains targeted sales for every person. In our example, we want to match these two tables based on column “Sales Person” and create a new table which will have all data aggregated (“Sales Person”, “Sales Actual”, “Sales Target”). We can see that the first table has some misspelled names and we want to match them with correct names in the second table based on their similarity.
Let’s now create an example of Fuzzy Lookup and explain how it works. First, we need to select a cell, which will be the first cell of a newly created table, then go to Fuzzy Lookup tab and click on Fuzzy Lookup button. We will get the following window opened on the right side:
To create our table, we have several steps to do:
How Do You Add Fuzzy Lookup In Excel
In the first part of Fuzzy Lookup window, we need to choose two tables which will be matched. In our case left table will be “Sales_Actual” and the right table will be “Sales_Target”. After that, we need to choose the columns which we want to match and click on the button between them. In our example, we want to match tables based on similarity of columns “Sales Person”, so we will choose this column both in Left Columns and Right Columns. Once we do that, the table below will have one new row with these matching columns. In Output columns, we need to check columns that we want to be in a newly generated table: “Sales_Person”, “Sales_Actual” and “Sales_Target”.
There is also an option to choose field “FuzzyLookup.Similarity” which gives the percentage of similarity between two columns. In the end, we can choose Similarity Threshold (0-100%) which tells the function what level of similarity we want to match. After everything is set up, we can click go and get a table based on entered parameters:
As you can see in the picture, the new table is created from the first two chosen. It consists of 3 columns that we choose and column similarity which calculates the similarity of “Sales_Person” columns in the two tables in percentage. For example, “John Bryant” from the first table is matched with “John T. Bryant” from the second table as their similarity is equal to 92%. Also, names “Rachel Williams” and “Harry White” do not have similar values in the second table (based on similarity percentage – 50%), so no value is filled in “Sales Target” column for these two entries.
If we wanted to get these two values also in the matched table, we would have put smaller Similarity Threshold in Fuzzy Lookup window. In the following example, we put it at 0.2 (20%) which means that we want to match all names that have the similarity of 20% or greater. Here is the result:
Fuzzy Lookup Add-in For Excel Macbook
The output now is the same table, but with included names “Rachel Williams” and “Harry White” because their similarity to “Jason J. Williams” and “Harrison L. White” is greater than 20%.
How To Use Excel Fuzzy Lookup Add In
Are you still looking for help with the VLOOKUP function? View our comprehensive round-up of VLOOKUP function tutorials here.