Unique Values in Excel with dynamic parameter

0
votes

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?

1
excelexcel-formula
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)),"") – tigeravatar
that's gold man, thanks. can you put as an answer so I can mark it please? :) – al1en

1 Answers

1
votes

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)),"")