CODEWITHSUNDEEP
excelexcel-formula
al1en
al1en

Reputation: 511

Unique Values in Excel with dynamic parameter

I am using a formula to copy a unique values from a table into a column. This is the formula that I am using at the moment:

=IFERROR(INDEX(Table_Arch[customer], MATCH(0,COUNTIF($A$7:A7, Table_Arch[customer]), 0)),"")

So far, so good, I am getting the unique values. The question is, can this formula be modified somehow, to get only unique values but based on a different column from the Table_Arch?

Example: The table arch looks like:

A    :    B
------------
1    :    AK
1    :    AJ
2    :    AM
2    :    AI

The result that I need to get is Unique values only where there is "1" into the ColumnA from the Table_Arch.

I am not sure if this was clear enough, at the moment I am getting all the Unique values from column, What I need is unique values from columnB where 1 is in columnA.

No VBA please, is it possible to modify the current formula?

Upvotes: 0

Views: 50

Answers (1)

tigeravatar
tigeravatar

Reputation: 26640

Replace the Table_Arch[A] with the actual column of the table: 

=IFERROR(INDEX(Table_Arch[customer],MATCH(1,INDEX((COUNTIF($A$7:A7,Table_Arch[customer])=0)*(Table_Arch[A]=1),),0)),"")

Upvotes: 1

Related Questions