Return to Excel Formulas List

VLOOKUP Duplicate Values – Excel & Google Sheets

Download Example Workbook

Download the example workbook

This tutorial will demonstrate how to lookup duplicate values using VLOOKUP in Excel and Google Sheets. If your version of Excel supports XLOOKUP, we recommend using XLOOKUP instead as this allows for a much easier solution..

vlookup duplicate values Main

VLOOKUP Nth Match

The VLOOKUP Function always returns the first match. In order to return duplicate values (or the nth match) we need:

  1. A new unique identifier to differentiate all duplicate values.
  2. A helper column containing a list of unique IDs that will serve as the new lookup column (first column) of the table array.
  3. A modified lookup value to match the format of the unique IDs.

This is how the final solution will look like:

vlookup duplicate values 01

 

Now, let’s walk through the solution:

Unique IDs using COUNTIF

Let’s say we want to lookup the second score for Student ID = 2021-A in this dataset:

vlookup duplicate values 02

First, we will create unique IDs in column B by joining the original student ID (column C) with a COUNTIF Function:

vlookup duplicate values 03

The COUNTIF Function counts the number of each Student ID. By locking one cell reference in the COUNTIF Function, but not the other, we identify each instance of a duplicate Student ID with a unique number:

vlookup duplicate values 04

Then we simply use the & operate to concatenate everything together as in the previous formula.

 

VLOOKUP Nth Match

Now, we can VLOOKUP the 2nd match using this formula where the lookup value matches the format of the new Unique ID:

vlookup duplicate values 01

VLOOKUP All Matches

Instead, what if you want to lookup all matches?

You could manually list out each match that you want to find:

vlookup duplicate values 05

This might be an acceptable solution, but what if you don’t know how many matches exist?  This formula will output all matches for “2021-A”:

vlookup duplicate values 06

 

Let’s walk through the formula above:

ROW Function

We use the ROW Function to return the row number of a cell or a range. If we want a formula that can create a list of consecutive counting numbers starting from 1, we can input a whole row reference in the ROW Function and copy and paste it down the column.

vlookup duplicate values 07

New Lookup Value

Next, use the result of the ROW Function is combined with the lookup value (Column G Header) to create your unique lookup value.

vlookup duplicate values 08

Note: Make sure that the original lookup value is in absolute reference (e.g., $G$2).

 

VLOOKUP Function

Next, perform the VLOOKUP:

vlookup duplicate values 09

IFNA Function

Notice the #N/A errors for values that don’t exist. Next, let’s use the IFNA Function to output blank instead:

vlookup duplicate values 10

Combining all functions results to our original formula:

 

There’s a more convenient and simpler solution than the formula above, and that is by using the new FILTER Function. We can use the FILTER Function to filter the data and extract the duplicates in one go, but this function is currently only available to Microsoft 365 version. If you have Microsoft 365, then you should use the XLOOKUP Function instead.

 

VLOOKUP Duplicate Lookup Values

Now, what if we want to match duplicate lookup values to their corresponding instances from the lookup column?

In this case, we apply the same method from the previous sections to the lookup value:

vlookup duplicate values 11

 

VLOOKUP nth Match in Google Sheets

All VLOOKUP formulas that we have discussed above work the same way in Google Sheets.

vlookup duplicate values G Sheet