INDEX MATCH function, avoid duplicate returns

[certavi]

Hi everyone!

I'm working on a sheet for work. I am on my last task and can't seem to figure this one out. I'm hoping someone can help:

I have a data sheet with all the data. In my current sheet I would like it to return a list of all the names matching these two criteria. My formula is as follows:

=INDEX('Horse data'!B2:B100, MATCH(1, ($I$13='Horse data'!C2:C100) * ($K$13='Horse data'!D2:D100), 0))

The problem I have with it is that it returns duplicates, because for 2 or 3 cells, the first best match is the same, until my search area has moved down enough to continue on to the next match.

Is there any way to rewrite this formula to return a list of 'unique distinct values' and avoid duplicate returns?

Preferably without adding more columns, but if there's no other way I'd accept any solution!

[#2 · Accepted solution]

@certavi 

If you are on Excel with dynamic arrays that could be

=UNIQUE(FILTER('Horse data'!B2:B100,
  (I$13='Horse data'!C2:C100) * ($K$13='Horse data'!D2:D100)
))

[#3]

@SergeiBaklan Yes, that works great! Thanks a lot!

[#4]

@certavi , you are welcome

[#5]

Hello, is there a way to tdo this in excel 2007 version? @SergeiBaklan 

[#6]

The problem gets complicated in Excel 2007. To simplify it, I used an auxiliary column, and array-entered the following formula in cell L2, then copy it down. The formula returns the values from column B that satisfy the two criteria.

=IFERROR(INDEX('Horse data'!B$2:B$100,SMALL(IF(($I$13='Horse data'!C$2:C$100) * ($K$13='Horse data'!D$2:D$100),ROW('Horse data'!B$2:B$100)-ROW('Horse data'!B$2)+1,""),ROWS(L$2:L2))),"")

  The next step is to avoid duplicate returns. I did that by copying down another array-entered formula in column M:

=IFERROR(INDEX(L$2:L$100,SMALL(IF(IFERROR(MATCH(L$2:L$100,L$2:L$100,0),0)*(L$2:L$100<>"")=ROW(L$2:L$100)-ROW(L$2)+1,ROW(L$2:L$100)-ROW(L$2)+1,""),ROWS(M$2:M2))),"")

[#7]

@SergeiBaklan  im having a similar issue with an excel formula. When I drag this down, I am having duplicate returns until I another value hit. Do you know how the formula should look to delete the duplicates?

=INDEX('Walleye OMS'!B2:B303,MATCH('final (2)'!A3,'Walleye OMS'!U2:U303,0))

[#8]

@cweaver345414,

MATCH always returns the first match for the item sought. That's why it keeps duplicating the value returned.

I assume that you are copying the formula down in worksheet final (2) starting in row 3.

=INDEX('Walleye OMS'!B$2:B$303,AGGREGATE(15,6,(ROW('Walleye OMS'!U$2:U$303)-ROW('Walleye OMS'!U$2)+1)/('Walleye OMS'!U$2:U$303='final (2)'!A3),COUNTIF('final (2)'!A$3:A3,'final (2)'!A3)))

Brad

[#9]

@Brad Yundt wow that worked thank you so much!!

[#10]

@cweaver345414 @Brad Yundt 

Hi Brad, it looks like im running into a similar issue but on a different spreadsheet. im trying to grab all equity quantities and put it on a separate spreadsheet. Do you by chance have any idea what I may be missing?

=INDEX('Walleye JPMcopy)'!L$2:L$643,AGGREGATE(15,6,(ROW('Walleye JPMcopy)'!D$2:D$643)-ROW('Walleye JPMcopy)'!D$3)+1)/('Walleye JPMcopy)'!D$2:D$643="EQUITIES"),COUNT('Walleye JPMcopy)'!D$3:D3,'Walleye JPMcopy)'!D3)))

[#11]

@cweaver345414,

It would be helpful to see your actual workbook and know exactly where you want to put the formula.

I assumed that you are retrieving data from worksheet Walleye JPMcopy) column L, and are looking to return only those values that pertain to EQUITIES. I also assumed that your data start on row 2, and that the formula is also placed on row 2, possibly on a different worksheet. If so, you might try copying down:

=IFERROR(INDEX('Walleye JPMcopy)'!L$2:L$643,AGGREGATE(15,6,(ROW('Walleye JPMcopy)'!D$2:D$643)-ROW('Walleye JPMcopy)'!D$2)+1)/('Walleye JPMcopy)'!D$2:D$643="EQUITIES"),ROWS(D$2:D2))),"")

The suggested formula returns an empty string (looks like a blank) when there are no more EQUITIES.

If I guessed incorrectly, please post a sample workbook and tell me exactly which cell needs the formula. 

[#12]

@Brad Yundt 

Hi Brad,

Thank you for the input. I am trying to copy the data into cell G3 in spreadsheet Final (2) from sheet Walleye JPMcopy). I put "EQUITIES" because I was unsure where to select. Please let me know if you need more information.

[#13]

I want to see a copy of the actual workbook along with a small selection of the results you want to return. When looking at the formula you were trying to use, I couldn't tell where the formula was being placed. Nor could I tell what the exact name of the worksheet might be--I now see that worksheet final (2) has a closing right parenthesis, something that was missing in your original formula.

It is also not clear whether I'm supposed to return information on all the EQUITIES, or all the info that is available in worksheet Walleye JPMcopy.

When I look at your workbook, all care about is worksheets final (2), Walleye JPMcopy and whichever worksheet will be containing the formula. Similarly, all I care about on worksheet Walleye JPMcopy are columns D and L, and column L could have the numbers 1 through xx as their data. I don't need a lot of data--I used ten rows in my test workbook.

What is important is the layout. Where does the data start and end? Where does the formula go? What values do you expect the formula to return? Is the formula only supposed to return EQUITIES? Should it return other types of investments, too?

[#14]

@cweaver345414,

This is my test workbook. Feel free to mark it up so I know what the formula is supposed to be doing.

Brad

[#15]

@Brad Yundt im not able to attach my spreadsheet for some reason but hopefully these screenshots help. I am just trying to pull all quantities (equities) from Walleye sheet in to final sheet.

[#16]

Have you tried the last formula I suggested? It should be working for the problem you show in the screenshots once you correct the names of the worksheets.
=IFERROR(INDEX('Walleye JPMcopy)'!L$2:L$643,AGGREGATE(15,6,(ROW('Walleye JPMcopy)'!D$2:D$643)-ROW('Walleye JPMcopy)'!D$2)+1)/('Walleye JPMcopy)'!D$2:D$643="EQUITIES"),ROWS(D$2:D2))),"")

[#17]

Yeah but it looks like it isnt pulling anything from Walleye JPMCopy) sheet.@Brad Yundt 

[#18]

Please disregard! I did a Vlookup and that seemed to work!

[#19]

@cweaver345414,

"I did a Vlookup and that seemed to work!"

---

That's why you should post a sample workbook in the thread, or a link to one in your OneDrive. You probably would have gotten the right answer the first time.

Glad you got things sorted out.

Brad

[#20]

@Brad Yundt@cweaver345414 it looks like i am running into a small issue with another formula. im trying to get all option quantities in the JPM Quantity tab, would you know what formula to use?