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.


Download Practice Workbook

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


3 Formulas with INDEX-MATCH to Deal with Duplicate Values in Excel

Formula 1: Mark Duplicate Values 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.

Duplicate Values with INDEX, MATCH, IF, and COUNTIF

Step 1:

⏩ Activate Cell E5

⏩ Type the formula given below-

=IF(COUNTIF($C$5:C6,C6)>1,"Duplicate of "&INDEX($B$5:B6,MATCH(C6,$C$5:C6,0)),"Original")

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

Duplicate Values with INDEX, MATCH, IF, and COUNTIF

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(C6,$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-

{2}

INDEX($B$5:B6,MATCH(C6,$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-

{Ron}

COUNTIF($C$5:C6,C6)>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,C6)>1,”Duplicate of “&INDEX($B$5:B6,MATCH(C6,$C$5:C6,0)),”Original”)

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

{Original}

Read More: Multiple Criteria in Excel Using INDEX, MATCH, and COUNTIF Function


Formula 2: Match the Duplicate Values with INDEX, ROW, and SMALL 

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 value from a given set of values.

Step 1:

⏩ In Cell C16 write the given formula-

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

📔 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 here to learn how to define names in Excel.

⏩ Click the Enter button for the output.

Duplicate Values with INDEX, ROW, and SMALL

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}

Read More: Index Match Multiple Criteria in Rows and Columns in Excel


Similar Readings


Formula 3: Extract Data 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

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

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

Duplicates in Two Columns with INDEX+MATCH

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}

Read More: How to Use INDEX-MATCH Formula in Excel to Generate Multiple Results


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. Visit our website Exceldemy.com to explore more.


Related Readings

Mithun

Mithun

Hello! I am Md. Sourov Hossain Mithun. Welcome to my profile. Currently, I am working at Exceldemy as an Excel and VBA Content Developer. Excel is amazing software. Here I will post excel related useful articles. I am a graduate of Bangladesh University of Engineering and Technology. I love to learn new things and work with them. Thank you.

6 Comments
  1. Hi, nice tutorial, but I am a begginer.

    I would like to make excel table for ordinary tennis league, but if 2 teams are equal with same points than it should take into account their mutual match, so who won the match is higher ranking. So, I have 12 teams, namely:
    Team 1
    Team2
    Team3
    Team4
    Team5
    Team6
    Team7
    Team8
    Team9
    Team10
    Team11

    After entering the results for each round, let Excel itself sort the Teams in the following order:
    a) Team with most points is highest ranking.
    b) If 2 teams with the same nr. of points, the head-to-head is then watched and whoever won is higher ranking. If the teams have not yet played each other or have result for draw, then skip to c).
    c) If the teams are still the same, then they are judged by goal difference – higher difference is higher ranking.
    d) If the teams are still the same, then see who received fewer goals – who received less goals is higher ranking..
    e If the teams are still the same, then see who scored more goals – who scored more goals is higher ranking.
    f) If the teams are still the same, then they occupy the same ranking place.

    Here is link for demo file:
    https://docs.google.com/spreadsheets/d/1MvQz0USGOsceyaHEFGpgTNRPL9hxbGGR/edit?rtpof=true&sd=true

    If you have any solution / formula, pls let me know

    Regards

    • Hello Mat, thanks for your feedback. The problem you mentioned will need a complex formula. You will have to apply a formula like this:
      =IF(SUM(–(MAX(AC2:AC12)=AC2:AC12))=1,INDEX(T2:AC12,MATCH(MAX(AC2:AC12),AC2:AC12,0),1)).

  2. Hello Mithun,
    I’m trying to do something similar but am having a difficult time applying. I hope you can help with the following:

    I have a sheet with thousands of rows representing individual books. However, multiple individual items (books) can be a part of a single title. Each item has usage numbers. Also, each row has unique key for the catalog (catalog ID) and a unique key for the item (item barcode).

    I’m looking for two formulas. One to count the number of items per title (according to catalog id). And another to sum the total item usage of all items on the same title.

    I’ve included a sample sheet to help illustrate:
    https://docs.google.com/spreadsheets/d/1IZOKV46uFo52R0w9dMvHoK1Luj-VHZpt/edit?usp=sharing&ouid=118360611201546687127&rtpof=true&sd=true

    I’m looking for formulas to return the values seen in columns E and F.

    Any help will be greatly appreciated!

  3. Hello Mithun,

    You have created some great instructions! So I’m reaching out for assistance for, possibly, the second time.

    If you have already seen a comment by me today, I apologize for the duplication. I posted something earlier but there was no indication that a comment had been submitted. Given your expertise, I thought it would be worth trying again!

    I have a sheet with thousands of rows, each row with data about a single item (book). Each item has information such as number of uses and the catalog title to which that item is associated. The same catalog title can be associated with multiple items (think of a multi-volume set of books, all with the same title). Each item has a unique key (item barcode) and each title has a unique key (catalog id).

    I am looking for two formulas to help examine usage. One formula will simply count the number of items associated with each title (according to catalog id), returning that value to each row. The other formula will sum the total number of uses of each item associated with that same title, returning that value to each row.

    I’ve included an example sheet to demonstrate:

    https://docs.google.com/spreadsheets/d/1IZOKV46uFo52R0w9dMvHoK1Luj-VHZpt/edit?usp=sharing&ouid=118360611201546687127&rtpof=true&sd=true

    On the example sheet, I’m looking for formulas to return the values in columns E and F.

    Any help will be most appreciated!

    • Hi MICHAEL,
      Thanks for your feedback.

      To count the number of items associated with each title (according to to catalog id), use this formula: =COUNTIF($B$2:$B$27,B2)

      And to sum the total number of uses of each item associated with that same title, use this formula: =SUMIF($B$2:$B$27,B2,$D$2:$D$27)

  4. the duplicates in the first example seem wrong. The dups are being identified by states and Michigan is marked as a duplicate, but it is not. Alabama is a duplicate, but is tagged as original.

Leave a reply

ExcelDemy
Logo