I took out the WHEN table2.category IS NULL clause because I only want it to pull the category from table2 when the lookup_value exists in description, otherwise pull the table1 category. NULL doesn't matter to me.
Unfortunately, it still always pulls the category from table1.
SELECT code, description, CASE WHEN description LIKE ('%' || lookup_value || '%') THEN table2.category ELSE table1.category END FROM table1 LEFT JOIN table2 USING (code);
If you provide a self-contained example with records you expect and don't expect to match better help can be given.