If criteria not met then another list

I'm trying to get a modification to the following formula:

=UNIQUE(SORT(TOCOL(FILTER(Table2[[Color 1]:[Color 4]],Table2[State]=$A$3),1)))


Formula currently sort/filters all unique values in columns Color1 thru Color4 if State column matches criteria from "A3"....pretty straight forward.

What I need: If criteria is not an option in State column then Colors if State selected should revert to Colors (ALL).

Maybe this:

=IF($A$3="",UNIQUE(SORT(TOCOL(Table2[[Color 1]:[Color 4]],1))),UNIQUE(SORT(TOCOL(FILTER(Table2[[Color 1]:[Color 4]],Table2[State]=$A$3),1))))

But why start a new thread? Would have been better asked in the thread where you got the formula you want amending.

Administrative Note:

This is not a new issue, therefore does not warrant a new thread.

You are allowed only ONE thread per issue here.

Please see Forum Rule #2 about thread duplication.

I am closing this thread, but you may continue here in the original thread: https://www.excelforum.com/excel-for...teria-met.html

Thread closed.