Forum Discussion
Solved
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:$...
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)),"")
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.