How to Find Duplicates Using VLOOKUP in Excel

To find duplicates using VLOOKUP in Excel, follow these steps:

1. Open your Excel workbook and make sure you have the data you want to check for duplicates.
2. Sort your data in ascending order by the column in which you want to find duplicates. To do this, click on the column header, then go to the "Data" tab, and click "Sort A to Z."

Now, let's move on to the example:

Example: Find Duplicates Using VLOOKUP

For this example, let's assume we have the following data in columns A and B:

|  A   |   B   |
|------|-------|
| ID   | Name  |
| 1    | John  |
| 2    | Jane  |
| 3    | Joe   |
| 4    | Jack  |
| 2    | Jane  |
| 5    | Jill  |
| 3    | Joe   |

We want to find duplicates in the ID column.

3. In an empty cell in column C (let's say C2), enter the following VLOOKUP formula:

=IFERROR(VLOOKUP(A2, A$1:A1, 1, FALSE), "Not Duplicate")

4. Press Enter. The formula will return "Not Duplicate" for the first row, as there is no previous data to compare.
5. Copy the formula in C2 and paste it into the other cells in column C, corresponding to the data in column A.

The updated table will look like this:

|  A   |   B   |       C        |
|------|-------|----------------|
| ID   | Name  | Not Duplicate  |
| 1    | John  | Not Duplicate  |
| 2    | Jane  | Not Duplicate  |
| 3    | Joe   | Not Duplicate  |
| 4    | Jack  | Not Duplicate  |
| 2    | Jane  | 2              |
| 5    | Jill  | Not Duplicate  |
| 3    | Joe   | 3              |

6. The duplicates in the ID column are now visible in column C. In this example, the IDs 2 and 3 are duplicates.

Note: This method works best when your data is sorted in ascending order. If your data is not sorted, the VLOOKUP may not find all duplicates correctly.