Search code examples
excellistuniquearray-formulas

Return Multiple Unique Matches in Excel without Array Formula

Given an Excel table of shape

Col. A   Col B   Col. C   Col. D   Col. E
x        2       x        2        3
x        3       y        7
y        7       z        -5
x        2
z        -5

I want to return the first unique hit in Column B for argument "x" in Column D, the second unique hit in Column B for argument "x" in Column E and so forth.

The formula I'm currently using in cell D1 for this is

{=IFERROR(INDEX($B$1:$B$5,MATCH(0,COUNTIF($C1:C1,$B$1:$B$5)+($A$1:$A$5<>$C1),0)),"")}

which is working.

The problem I'm having is that since this is an array formula and since I'm analyzing a decent amount of data computation time for my sheet is too high.

Is there an alternative for this functionality avoiding an array formula?

Thanks!

Solution

  • Haven't got time to test this properly, but if you have Excel 365 you can use a single formula per row and it may be faster:

    =TRANSPOSE(UNIQUE(FILTER(B1:B10,A1:A10=C1)))

    in D1.

    EDIT

    To pull the formula down, you need static references as OP has pointed out. Probably should check for empty cells in column C as well, so formula becomes:

    =IF(C1="","",TRANSPOSE(UNIQUE(FILTER(B$1:B$10,A$1:A$10=C1))))