Since you've underlined "expert", then let me interject that merging cells in this manner is a no-no in a database or lookup table, it's not something an expert would do. Merging cells is for "prettying up" final reports and outputs, not for raw / reference data that needs to be robust.

So, your true database would have yellow in B4 and B5, too. Now, all normal lookup functions work correctly without the need of magic.