Forum Discussion

DonMirabella's avatar
DonMirabella
Copper Contributor
Oct 16, 2020

Reference Question

I am trying to figure out how to use the Lookup formulas in Excel to get what I need.   I have a 2x100 table of values. The left column, "A", has a series of unique numbers. The right column. "B"...
Formulas and Functions
mtarler's avatar
mtarler
Silver Contributor
Oct 16, 2020

DonMirabella  You can use the following 2 formulas:

to create the unique list from B:

=UNIQUE(B:B)

Then to create the list you want:

=INDEX(TRANSPOSE(FILTER(A:B,B:B=E1)),1)

where the original list is in columns A and B and the new lists are in E and F.  You then copy that formula down

DonMirabella's avatar
DonMirabella
Copper Contributor
Oct 16, 2020

mtarler We don't have Excel 365 so no Unique function available.

  • SergeiBaklan's avatar
    SergeiBaklan
    Diamond Contributor
    Oct 16, 2020

    DonMirabella 

    One more variant

    In G4

    =IFERROR(
   INDEX($B$2:$B$101,
     AGGREGATE(15,6,1/(COUNTIF($G$3:G3,$B$2:$B$101)=0)*(ROW($B$2:$B$101)-ROW($B$1)),
     1)
),"")

    and drag it down till empty cell appears.

    In H4

    =TEXTJOIN(", ",1,IF($B$2:$B$101=$G4,$A$2:$A$101,""))

    and also drag down

Resources