I understand what you're suggesting, but the formula you posted would return
an error if the test values don't match any of the pairs. For example, in
your posted scenario, the formula returns #NA.

Also, I considered a separator character and a few other modifications to
the posted formula, but decided against them for a few reasons:

1)I suspect the values of "red", "green", etc are only posted to give us
something easy to work with. The actual values may be different.

2)I would hope there would be control over the content of the cells (data
validation, import from a database, etc), obviating the need for error traps.

3)Without ALL of the details about possible cell content, we could create
some huge formulas that attempt to trap possibilities that would never occur.

Also, if somebody could enter "RedB" in one cell and "lue" in the next, why
not "Red|" and "Blue"?

Let's see if we get more specifics about the data.

***********
Regards,
Ron

XL2002, WinXP


"Bernd" wrote:

Hello,

I suggest to introduce a character which does not appear, or you could
get a wrong hit if a1=RedB and b1=lue:

=VLOOKUP(A1&"|"&B1,{"Red|Blue",10;"Green|Orange",2 0;"Purple|Yellow",
30},2,)

Regards,
Bernd