Forum Discussion
INDEX MATCH function, avoid duplicate returns
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) ))
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
Brad Yundt wow that worked thank you so much!!
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)))
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.
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.
