Vlookup and If Combination

[imerial]

Hey Guys,

I'm working on a formula, but I'm having trouble figuring out how it should work. I attached a sample file. What I'm trying to do is lookup column a on sheet 1 and find it on sheet 2, then if column d says red, it should say 'r' in colors, if column e says blue it should say 'b' in colors, etc... I'm not sure what the best way to do this is. I think I can use a series of vlookups, with 1 vlookup for each color column, but this may be cleaner with an if statement. Any thoughts?

Thanks.

[Jonmo1]

Try this

On sheet 2..
In H2, put
=TRIM(LEFT(D2)&" "&LEFT(E2)&" "&LEFT(F2)&" "&LEFT(G2))
And fill down to the end.

Then on Sheet 1
In C2 put
=VLOOKUP(B2,Sheet2!B:H,7,FALSE)

[imerial]

That's a simple way to do it, thanks!

[Jonmo1]

You're welcome.