INDEX-MATCH with Duplicate Values in Excel (3 Quick Methods)

In Microsoft Excel, there are several suitable methods to find duplicate values with various functions. In this article, you’ll learn several handy methods with INDEX-MATCH to find matches or duplicate values in Excel with suitable examples and proper illustrations.

1. Finding Duplicate Values in Excel with INDEX, MATCH, IF, and COUNTIF

Let’s get introduced to our dataset first. Here, I have placed some salespersons’ states and sales within 7 rows and 3 columns. Now we’ll find duplicate values by using the INDEX, MATCH, IF, and COUNTIF functions. If no duplicate is found then the formula will show “Original” and if there is any duplicate then it will show “Duplicate”. The INDEX function returns a value or the reference to a value from within a table or range. The MATCH function searches for a specified item in a range of cells and then returns the relative position of that item in the range. The IF function is used to check a condition whether it’s met or not, and then it returns one value if it’s true and another value if it’s false. And the COUNTIF function counts the number of cells in a range that meets a given criterion.

Step 1:

⏩ Activate Cell E5

⏩ Type the formula given below-

⏩ Then just hit the ENTER button to get the output.

Step 2:

⏩ Now double-click the Fill Handle icon as shown in the image below to copy the formula for the other cells.

Finally, you will see that the duplicates are found.

 💡 Formula Breakdown:

➥ MATCH(C5,$C$5:C6,0)

The MATCH function finds the first occurrence of a value in a stack of values and returns the relative row number where the value is found. So it returns as-

{1}

➥ INDEX($B$5:B6,MATCH(C5,$C$5:C6,0))

The INDEX formula returns a value from a specified row of a specified stack of cells. Like, INDEX(B1:B10,5) would return the 5th value in the range B1:B10. From our array it will return as-

{Peter}

➥ COUNTIF($C$5:C6,C5)>1

The COUNTIF function will look at all the states so far and counts up how many times the current value has occurred.  If Excel sees the current value for the first time, the formula returns 1. And as 1 is not  greater than 1, so it will show-

{FALSE}

➥ IF(COUNTIF($C$5:C6,C5)>1,”Duplicate of “&INDEX($B$5:B6,MATCH(C5,$C$5:C6,0)),”Original”)

Finally, the IF function will show “Original” for FALSE and Duplicates for TRUE. So it returns-

{Original}

2. Joining Excel INDEX, ROW, and SMALL Functions for Duplicate Values

Here, we’ll find values for the nth duplicate by using the INDEX, ROW, and SMALL functions. The ROW function returns the row number for reference. And the SMALL function is used to return the nth smallest or minimum value from a given set of values.

Step 1:

⏩ In Cell C16 write the given formula-

📔 Note:

Here we have defined array and reference names that will be helpful to use the formula-

Sales= D5:D11
State= C5:C11
StateName= C13

⏩ Click the Enter button for the output.

Step 2:

⏩ To copy the formula now just use the Fill Handle tool.

⏬ Formula Breakdown:

➥ INDEX(State,1,1)

The INDEX function will return the value according to its relative row number 1 and column number 1. That is-

“Arizona”

➥ ROW(INDEX(State,1,1))

Then the ROW function will find its original row number-

{5}

➥ ROW(State)-ROW(INDEX(State,1,1))+1

As the upcoming INDEX function works for row number of a particular array, not for Excel row number, that is why we have to give the INDEX function the row number by applying this formula. The output is-

{1;2;3;4;5;6;7}

➥ IF(State=StateName,ROW(State)-ROW(INDEX(State,1,1))+1)

Then the IF function will do the logic test for the value “Alaska”. If found then it will show the position number whether it will show FALSE and that will return as-

{FALSE;FALSE;3;FALSE;FALSE;6;FALSE}

➥ SMALL(IF(State=StateName,ROW(State)-ROW(INDEX(State,1,1))+1),B16)

The SMALL function will show the lowest number among them which is-

{3}

➥ INDEX(Sales,SMALL(IF(State=StateName,ROW(State)-ROW(INDEX(State,1,1))+1),B16))

Finally the INDEX function will extract the value according to the output of the SMALL function, that returns-

{7832}

3. Extracting Values Based on Duplicates in Two Columns with INDEX-MATCH

In our last method, we’ll extract data based on duplicates from a column. I’ll extract the sales values for the states of Florida and Alabama. For that, we’ll use the INDEX, and MATCH functions.

Step 1:

⏩ Write the formula given below in Cell C14–

⏩ Later, press the Enter button to get the result.

Step 2:

⏩ Finally, use the Fill Handle tool to copy the formula.

⏬ Formula Breakdown:

➥ MATCH($B14,C5:C11,0)

The MATCH function will find the relative position for the state “Florida” from the state column, which returns-

{4}

➥ INDEX(C5:D11, MATCH($B14,C5:C11,0),2)

Finally, the INDEX function will extract the sales value according to row number 4 and column number 2 relative to the array C5:D11. That will return as-

{2675}

Download Practice Workbook

You can download the Excel workbook that we’ve used to prepare this article.

Conclusion

I hope all of the methods described above will be good enough to use INDEX+MATCH with Duplicate Values in Excel. Feel free to ask any questions in the comment section and please give me your feedback.

<< Go Back to INDEX MATCH | Formula List | Learn Excel