Effective dates, complex requirements
I explain how to do this here: http://blogs.lobsterpot.com.au/2014/07/08/ssis-lookup-transformation-in-t-sql/

The basic concept is to use OUTER APPLY with TOP (1). In your case you may need to start with your list of people that you're looking up. Like this:

SELECT p.Person, ISNULL(c1.color, c2.color) AS color
FROM (VALUES ('Nina')) AS p(Person)
OUTER APPLY 
    (SELECT TOP (1) f.color
    FROM fav_colors AS f
    WHERE f.name = p.Person
    AND f.eff_date &lt;= @date
    ORDER BY f.eff_date DESC) c1
OUTER APPLY
   (SELECT TOP (1) f.color
    FROM fav_colors AS f
    WHERE f.name = p.Person
    AND c1.color IS NULL
    ORDER BY f.eff_date ASC) c2


The post details how the Query Optimizer will only do the second lookup if the first one fails. It's really very efficient with the right index in place (on (name, eff_date) include (color)).

Oh - if color can be NULL legitimately, then test a different column in the c2 sub-query.