How to Use VLOOKUP to Find Duplicates in Google Sheets

In this tutorial, we will show you exactly how to use VLOOKUP to find duplicates in Google Sheets. Simply follow the steps below.

Use VLOOKUP to Find Duplicates in Google Sheets

Follow the steps below to find duplicates in Google Sheets using VLOOKUP.

1. Organize Your Data Into Two Columns

In Google Sheets, arrange your data into two columns. Each column should represent a different category of information. This organization is crucial for accurately comparing your data.

2. Identify the Columns to Compare

For this example, we will compare the fruits listed in "MARKET A" (in column A) against those in "MARKET B" (in column B). This will help you find duplicates between the two markets.

3. Label the Next Column as "Duplicates" 

Label the column next to "MARKET B” as "Duplicates." This column will indicate whether each fruit from "MARKET A" is also found in "MARKET B."

4. Write the Formula in the First Cell of the "Duplicates" Column

We will start by entering the VLOOKUP formula in the first cell of the "Duplicates" column (C2). The general structure of the VLOOKUP formula is: =VLOOKUP(search_key, range, index, [is_sorted]).

5. Start the Formula With =VLOOKUP and Identify the "Search_Key"

The search key is the value we want to find in the other column, which in this case is the first fruit in "MARKET A" located in cell A2. Start the formula by typing =VLOOKUP(A2, ).

6. Define the "Range" by Highlighting the Cells in Column B

The range is the column where you want to search for the value, which in this case is column B containing the fruits in "MARKET B." After typing =VLOOKUP(A2, you can simply highlight the cells in column B to select the range.

7. Set the "Index’" to 1

In our example, the formula would be =VLOOKUP(A2, B2:B21, 1, ). The index is set to 1 because we're searching in column B, which is the first column in our range.

8. Set the "Is_Sorted" Parameter to FALSE

Since we want an exact match, set the "is_sorted" parameter to FALSE. This tells the VLOOKUP function to look for an exact match of the search key, so our formula will look like =VLOOKUP(A2, B2:B21, 1, FALSE).

9. Click Enter to Process the VLOOKUP Formula

After entering the full formula: =VLOOKUP(A2, B2:B21, 1, FALSE), click Enter. If A2 is not found in "MARKET B," the result will show #N/A.

10. Highlight the Range in the Formula Bar and Press Fn+F4 to Turn It Into an Absolute Reference

Press Fn+F4 to change the range to absolute references. This will keep the range constant when we copy the formula to other cells.

11. Add the =IFERROR() Function to Handle #N/A Errors

Adjust the formula to display blank cells instead of #N/A by using =IFERROR(VLOOKUP(A2, B2:B21, 1, FALSE),""), then press Enter. This will leave the cells blank if some fruit from "MARKET A" is not found in "MARKET B."

12. Copy and Paste the Complete Formula to the Rest of the Cells in the "Duplicates" Column

Copy the cell with the complete formula =IFERROR(VLOOKUP(A2, B2:B21, 1, FALSE),"") using Ctrl + C, then highlight the remaining cells and press Ctrl + V to paste. This will check all fruits in "MARKET A" for duplicates in "MARKET B."

13. Review for Duplicates

If a cell contains a fruit name, it means that fruit is also in "MARKET B." On the other hand, a blank cell indicates no duplicates.

We hope that you now have a better understanding of how to use VLOOKUP to find duplicates in Google Sheets. If you enjoyed this article, you might also like our articles on how to make a burndown chart in Google Sheets and how to make a to-do list in Google Sheets.