Hi, I'm trying to set up a 1 column table (Table B) that looks up the unique values from another table (Table A).
Table A
[TABLE="width: 500"]
<tbody>[TR]
[TD]Company[/TD]
[TD]Contract[/TD]
[/TR]
[TR]
[TD]Apple
[/TD]
[TD]123[/TD]
[/TR]
[TR]
[TD]Google[/TD]
[TD]456[/TD]
[/TR]
[TR]
[TD]Samsung
[/TD]
[TD]789[/TD]
[/TR]
[TR]
[TD]Apple[/TD]
[TD]ABC[/TD]
[/TR]
[TR]
[TD]Samsung[/TD]
[TD]DEF[/TD]
[/TR]
[TR]
[TD]Samsung[/TD]
[TD]DEF[/TD]
[/TR]
</tbody>[/TABLE]
Table B
[TABLE="width: 500"]
<tbody>[TR]
[TD]Select Company[/TD]
[TD]Samsung[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]789[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]DEF[/TD]
[/TR]
</tbody>[/TABLE]
In the sample above the user will select on Table B a company from a data validated drop down (Samsung) and the unique contracts associated with Samsung will populate (789, and DEF). My current formula is populating DEF twice.
{=IFERROR(INDEX(ContractList,SMALL(IF(CompanyList='cell where Samsung is selected',ROW(ContractList)-ROW(B$2)+1),ROWS(ContractList))),)}
Any help is appreciated.
Table A
[TABLE="width: 500"]
<tbody>[TR]
[TD]Company[/TD]
[TD]Contract[/TD]
[/TR]
[TR]
[TD]Apple
[/TD]
[TD]123[/TD]
[/TR]
[TR]
[TD]Google[/TD]
[TD]456[/TD]
[/TR]
[TR]
[TD]Samsung
[/TD]
[TD]789[/TD]
[/TR]
[TR]
[TD]Apple[/TD]
[TD]ABC[/TD]
[/TR]
[TR]
[TD]Samsung[/TD]
[TD]DEF[/TD]
[/TR]
[TR]
[TD]Samsung[/TD]
[TD]DEF[/TD]
[/TR]
</tbody>[/TABLE]
Table B
[TABLE="width: 500"]
<tbody>[TR]
[TD]Select Company[/TD]
[TD]Samsung[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]789[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]DEF[/TD]
[/TR]
</tbody>[/TABLE]
In the sample above the user will select on Table B a company from a data validated drop down (Samsung) and the unique contracts associated with Samsung will populate (789, and DEF). My current formula is populating DEF twice.
{=IFERROR(INDEX(ContractList,SMALL(IF(CompanyList='cell where Samsung is selected',ROW(ContractList)-ROW(B$2)+1),ROWS(ContractList))),)}
Any help is appreciated.