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

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

We have input some salespersons’ states and sales in the dataset. Now we’ll find duplicate values. If no duplicate is found, then the formula will show “Original” and if there is any duplicate then it will show “Duplicate”.

Steps:

• Select Cell E5
• Copy the following formula into it:

• Hit Enter to get the output.

• Double-click the Fill Handle icon (bottom-right corner of the cell) to copy the formula for the other cells.

• You will see whether duplicates exist in the dataset.

 💡 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}

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

Let’s find the duplicate values of a single given cell (in this case, a state) and list the sales amounts for it.

Steps:

• In Cell C16, insert this formula:

Note:

We have defined array and reference names that will be used in the formula for easier overview:

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

• Hit Enter.

• Copy the formula down with 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, we have to give the INDEX function the row numbers 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))

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

{7832}

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

Let’s extract the sales values for the states of Florida and Alabama.

Steps:

• Copy the formula given below in Cell C14:

• Hit Enter to get the result.

• Use the Fill Handle tool to copy the formula to the cell below.

⏬ 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.

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