If the controlling column(s) are all from the same table, I suggest a computed column. That leaves the defining logic in only a single place, and allows the calc'd value to be used just like any other column: in SELECT list, WHERE clause, ORDER BY clause, etc..

ALTER dbo.target_table

ADD target_column AS

case

when source_column_1 = 'ABC' then 'XYZ'

when source_column_2 = '123' then 'PDQ'

when source_column_3 = 'DEF' then 'BLAH'

when source_column_3 = 'FOO' then 'Fighters'

when source_column_3 = 'DOG' then 'CAT'

when source_column_3 = 'RED' then 'Blue'

when source_column_3 in ('UP','DOWN') then 'Strange'

when source_column_4 = 'ICE' then 'Tea'

when source_column_4 = 'PHX' then 'Phoenix'

when source_column_4 = 'YES' then 'Indeed'

when source_column_4 in ('CAR','BOS') then 'Stuff'

end

Then, for example:

SELECT target_column, ...

FROM ...

WHERE

target_column IN ('Tea', 'Indeed')

ORDER BY

target_column