keronra.blogg.se

1. HOW TO USE VLOOKUP IN EXCEL 2013 TO FIND MATCHES HOW TO
2. HOW TO USE VLOOKUP IN EXCEL 2013 TO FIND MATCHES INSTALL

Once we have formatted our data in Spreadsheet, we can start creating Fuzzy Lookup with two tables created.

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”). This is done by selecting the whole table and entering a name into a Name Box: After we have created two tables, they need to be named in order to be used in Fuzzy Lookup function. To do this select cells range, click on Insert tab and choose Table. Please note that the matching columns must be formatted as text.īefore being able to do a Fuzzy Lookup, we need to format our data into tables. This can be very time consuming and that’s the point where Fuzzy Lookup saves precious time. 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 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. Using of Fuzzy Lookup solves this problem, by matching columns based on their similarity. In this case, if we use standard VLOOKUP function, it will not match this two values because it’s looking for the exact match. 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”. 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:Īs 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. Once you have installed the Add-In, next time when you open an Excel it will automatically import Add-In.

HOW TO USE VLOOKUP IN EXCEL 2013 TO FIND MATCHES INSTALL

In order to enable this function, Microsoft created an Add-In which can be downloaded from the following link:Īfter you have downloaded the installation file, you need to open it and install following instructions.

HOW TO USE VLOOKUP IN EXCEL 2013 TO FIND MATCHES HOW TO

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.įuzzy Lookup is not a standard Excel function, therefore you can’t find it in your standard tabs and buttons. 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. As an output, Fuzzy Lookup returns a table of matched similar data in the chosen column. 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. Now let’s add this VLOOKUP formula in cell G2 to calculate the appropriate sales number based on the product id in cell F2.Excel Fuzzy Lookup Add-In is used to match similar, but not exactly matching data. Use of VLOOKUP function to get Exact Matches Or the VLOOKUP formulas stop working properly and you will get a N/A errors. The TRUE value indicates that you want to calculate the approximate match.īefore applying the above formula, you need to make sure that your sales column is sorted in ascending order. The number 2 is that your matched value is returned. C1:D6 is the data range that you are using. Note: the Cell F2 is the searched value which you want to get it the appropriate discount number. And to search for an approximate matches based on your specified sales number 60, just type the following VLOOKUP formula into cell G2. If you want to calculate the discount based on the quantity sold in your worksheet, and assuming that you want to search a sales number 60 to calculate its discount value, which doesn’t exist in your data A1:D6. Use of VLOOKUP function to Extract Approximate Matches If you still do not know what is exact or approximate matches in VLOOKUP function in Microsoft Excel, and it is recommend to have a quick look of my earlier post on how to use Excel Vlookup function.Īssuming that you have a sales data in cell Range A1:C6, and you want to find a sales number based on a product id, and you can use the following vlookup formula into a blank cell where you can to show the result.