Forum Discussion
INDEX MATCH Avoid Duplicate Returns
Hi!
I'm working with an index match formula for an array results. The formula I now use:
{=IF(ISERROR(INDEX($I$4:$L$48;SMALL(IF(COUNTIF($G$4:$G$15;$I$4:$L$48);MATCH(ROW($I$4:$L$48);ROW($I$4:$L$48));"");ROWS($B$4:B4));COLUMNS($I$3:I3)))=TRUE;"";INDEX($I$4:$L$48;SMALL(IF(COUNTIF($G$4:$G$15;$I$4:$L$48);MATCH(ROW($I$4:$L$48);ROW($I$4:$L$48));"");ROWS($B$4:B4));COLUMNS($I$3:I3)))}The problem is the results of the formula in the array return the same values (from the 'name' column) if the 'main status' and 'second status' rows are filled.
I need the result of 1 name only even though the 2 rows of status are filled. Is there any formula to do that? I used Excel 2013.
Thank you!
I simplified the formula by using AGGREGATE instead of SMALL. I don't believe it needs to be array-entered with AGGREGATE. Note that the comma is the list separator in my U.S. locale. If you open the attached workbook, the formula should be formatted correctly for your locale.
=IFERROR(INDEX($I$4:$L$48,AGGREGATE(15,6,(ROW($K$4:$L$48)-ROW($K$4)+1)/((COUNTIF($G$4:$G$15,$K$4:$K$48)+COUNTIF($G$4:$G$15,$L$4:$L$48))>0),ROWS(B$3:B3)),COLUMNS($B3:B3)),"")
3 Replies
- When asking a question, please post a sample workbook showing your data, its layout, what you have tried, and the desired results. Anybody trying to help will need a test workbook, and lots of them will go onto the next question if they need to create it from a screenshot. The amount of data you showed in the screenshot is plenty.
I simplified the formula by using AGGREGATE instead of SMALL. I don't believe it needs to be array-entered with AGGREGATE. Note that the comma is the list separator in my U.S. locale. If you open the attached workbook, the formula should be formatted correctly for your locale.
=IFERROR(INDEX($I$4:$L$48,AGGREGATE(15,6,(ROW($K$4:$L$48)-ROW($K$4)+1)/((COUNTIF($G$4:$G$15,$K$4:$K$48)+COUNTIF($G$4:$G$15,$L$4:$L$48))>0),ROWS(B$3:B3)),COLUMNS($B3:B3)),"")Brad_Yundt It works! Thank You. And thanks for the input sir really appreciate it.
