You are currently viewing How to Remove Duplicates in Excel Using Vlookup

Excel is an electronic tool that offers ease in managing numeric data. This large data contains collections of columns that essentially add up to make up rows. The data can be so large such that looking for duplicates can become very tiresome and inefficient. An excel document that has repeated records which are not distinct is very costly as it results in inconsistencies. It can also lead to calculations which are accurate but untruthful and this may restrict data integrity. Vlookup is a common concept that most users of Excel are not used to or may not be comfortable with. However, lookup operations are easy to master and comprehend. Some activities that can be done through commands in excel also have an alternative of being accomplished using Vlookup. Vlookup is used mainly for search operations. Just like its name, Vlookup is used to look up data in one vertical column and return data from another. Vlookup makes use of functions in excel to offer manipulation of data. These functions are usually fed with parameters depending on what task is to be accomplished. Through it, vertical lookups are easy to implement using vlookup. A basic lookup functionality follows uses a very simple syntax of "=Vlookup (lookup Value, range, column Index, Booleans). In most cases, the "=" and the "V lookup" keyword are essentially a must and applied when most functionalities are to be achieved. The loop up value is the value to be searched. The value can be an integer or string; however, it may also be using a cell reference with data of either kind. For the range, this implies that it's the block of cells that are to be looked up. The column index shows which column of data is to be used as a source of return data. The vlookup function, therefore, offers great flexibility and can be used to perform various tasks. One of the functions that Vlookup can accomplish is the identification of duplicates.

How to find duplicates in excel using Vlookup

Step 1: Ensure that MS Excel is among the installed programs on your computer.

In most cases if you try to open an excel document yet there is not at least one version of MS Excel available will make it impossible to effect the vlookup manipulations that are to be performed in this context.

Step 2: If MS Excel is already installed, open the program by clicking on its icon.

You should click the version that holds your document for those who may be having many different versions of MS Office installed in their computers.

This will initiate excel to be loaded into the computer's memory for the user by the user.

Step 3: After selecting on Excel application, use the file tab to open your document that contains duplicates in its data sets.

Step 4: Identify the block of cells whereby you want to apply vlookup on.

Then towards the right or left of the range add a column just against the range for better visualizations of vlookup function results.

For example, let's imagine that we have a range of cells filling two columns, that is, column C and D and we want to find duplicates the vlookup function will be =vlookup (C:C,D:D,TRUE,FALSE).

It's important to position the lookup cell on a location where the look up will generate results on an empty column just adjacent to the range that you want to check for the duplicates. For example, in our case, we can use column G to display for duplicates.

Step 5: Ensure that the vlookup function as shown in the previous step is typed at the formula bar when the cell cursor is on the desired cell that you want to return results to.

For example, in our case, we can use the cell just below our header for "check duplicates" column.

After typing the formula, press Enter key to display the result on the cell of the vlookup function.

Step 6: Now drag the bottom right corner of the cell in which a result has been posted. This will use the formula on all column values while checking for duplicates.

In instances when there are no duplicates found #N/A will be indicated showing that nil or no duplicates were found between cells in the two columns.

Step 7: Once duplicates are found and identified they can then be deleted or removed from the worksheet.

Deleting duplicates should be done carefully to avoid loosing of crucial data. Preferably always have a backup copy of your excel work to act as security.