Return to Excel Formulas List

XLOOKUP Duplicate Values

Download Example Workbook

Download the example workbook

This tutorial will demonstrate how to XLOOKUP duplicate values in Excel. If your version of Excel does not support XLOOKUP, read how to use the VLOOKUP duplicate values instead.

xlookup duplicate values

Lookup Duplicate Values

In Excel 365, looking up duplicate values is easier thanks to the FILTER Function. We will demonstrate the FILTER Function first and then demonstrate how to lookup duplicate values with XLOOKUP.

With the FILTER Function, just enter the array (e.g., Scores) that we want to return and the criteria range (e.g., Student ID) with the criteria (e.g., 2021-A).

=FILTER(C3:C7,B3:B7=E2)

filter lookup duplicate values

 

We can also nest the FILTER Function inside the INDEX Function and input a row_num (e.g., F3) to return an nth match that we want instead of returning all the matches.

=INDEX(FILTER(C3:C7,B3:B7=E3),F3)

index filter

 

The above scenarios showcase the FILTER Function as the more convenient solution to those types of problems in Excel 365. However, lookup scenarios that require a list of lookup values are often better solved through XLOOKUP formulas because we can convert them to dynamic array formulas, which makes them more flexible than the FILTER Function.

Let’s look first at the non-dynamic array solution to break down how it works before using the dynamic array XLOOKUP.

 

XLOOKUP – Duplicate Lookup Values

Let’s say you want to lookup a list of duplicate values using the XLOOKUP Function. Below, on the right, we have our lookup values. On the left we have the lookup table. We want to lookup each duplicate value and output in separate rows.

orig data

 

We can use the non-dynamic array formula solution below:

=XLOOKUP(F3&"-"&COUNTIF($F$3:F3,F3),$D$3:$D$7,$C$3:$C$7)

xlookup duplicate values

 

Let’s walk through the formula:

Unique ID – COUNTIF

First, we join the original ID (e.g., Student ID) with the COUNTIF Function to create a list of unique IDs that will distinguish the 1st entry of an item from its duplicates.

=B3&"-"&COUNTIF($B$3:B3,B3)

unique id countif

 

Locking one part of the range reference inside the COUNTIF Function enables us to count the occurrence of a value as the range expands.

=COUNTIF($B$3:B3,B3)

function countif

Now, we use the & Operator to stitch the COUNTIF Function to the original ID. We also added a separator (e.g., “-“).

=B3&"-"&D3

function countif unique id

 

Combining everything together results to our Unique ID Formula:

=B3&"-"&COUNTIF($B$3:B3,B3)

 

Duplicate Lookup Values – Nth Match

Just like with the original ID, we also need to create a list of unique IDs for the lookup values. Applying the same method:

=G3&"-"&COUNTIF($G$3:G3,G3)

function countif unique id new

 

XLOOKUP Function

Now, we input the new lookup array and list of lookup values to the XLOOKUP Function:

=XLOOKUP(H3,$E$3:$E$7,$C$3:$C$7)

xlookup duplicate looku pvalues

 

XLOOKUP – Duplicate Lookup Values (Dynamic Array)

Instead of adding new columns and copying or dragging formulas, we can convert the previous XLOOKUP Formulas into one dynamic array formula that can generate the same output.

Here’s how it looks:

=XLOOKUP(E3:E7&"-"&COUNTIF(OFFSET(E3,0,0,SEQUENCE(ROWS(E3:E7))),E3:E7),
B3:B7&"-"&COUNTIF(OFFSET(B3,0,0,SEQUENCE(ROWS(B3:B7))),B3:B7),
C3:C7)

xlookup duplicate lookup values dynamic array

 

Let’s walk through the formula above:

Just like with the previous method, we first need to create a list of unique IDs using the COUNTIF Function. The challenge would be how to create an array formula that will generate an array of range references (e.g., B3, B3:B4, B3:B5 and so on).

 

OFFSET-SEQUENCE-ROWS Formula

We can use the combination of the OFFSET, SEQUENCE and ROWS functions to return an array of range references.

Let’s start with the list of lookup array (e.g., B3:B7):

=OFFSET(B3,0,0,SEQUENCE(ROWS(B3:B7)))

Note: The formula above won’t work on its own. Excel can’t return an array of ranges or arrays, but it can evaluate it as we will see later.

 

ROWS Function

First, we need to determine the total number of rows using the ROWS Function.

=ROWS(B3:B7)

function rows

SEQUENCE Function

Next, we input the result of the ROWS Function in the SEQUENCE Function to generate a list of counting numbers, which also represents the number of cells per range in the array of ranges.

=SEQUENCE(C2)

function sequence

Array OFFSET Formula

Next, we input the results of the SEQUENCE Function to the height (4th argument) of the OFFSET Function to build the ranges.

=OFFSET(B3,0,0,C3:C7)

function offset

As mentioned earlier, this won’t work, but this is how the array of ranges is supposed to look like:

Ranges

 

We start with B3 and expand it for each height (e.g., C3:C7).

 

Note: OFFSET if a volatile function, which means that it recalculates whenever there’s a spreadsheet change even if it’s not related to the inputs of the OFFSET itself.

 

Array COUNTIF Function

Now that we have an array of ranges, we input it to the COUNTIF Function and return an array of nth occurrences.

=COUNTIF(OFFSET(B3,0,0,C3:C7),B3:B7)

function countif offset

Array Unique ID

Finally, we concatenate the array output of the COUNTIF Function to the lookup array (e.g., B3:B7) to generate the array of unique IDs.

=B3:B7&"-"&F3:F7

array unique id

Combining all the functions results to our array Unique ID Formula:

=B3:B7&"-"&COUNTIF(OFFSET(B3,0,0,SEQUENCE(ROWS(B3:B7))),B3:B7)

 

We also do the same for the lookup values:

=I3:I7&"-"&COUNTIF(OFFSET(I3:I7,0,0,SEQUENCE(ROWS(I3:I7))),I3:I7)

countif offset sequence rows functions

Combining everything together results to our original array formula:

=XLOOKUP(E3:E7&"-"&COUNTIF(OFFSET(E3,0,0,SEQUENCE(ROWS(E3:E7))),E3:E7),
B3:B7&"-"&COUNTIF(OFFSET(B3,0,0,SEQUENCE(ROWS(B3:B7))),B3:B7),
C3:C7)