I would like to know how I can take multiple cells together in a VLOOKUP
formula. Let me explain, I have 2 sheets:

Sheet1
A
1 Red
2 Blue
3 Yellow
4
5 =VLOOKUP(A1&A2&A3, Sheet2!A1:D5,4) answer 15


Sheet2
A B C D
1 Red Blue Yellow 10
2 Orange Purple Black 20
3 Pink White Silver 15
4
5

I want the VLOOKUP to return the value 10 from Sheet2, but it returns 15.
apparently the Vlookup cannot handle multiple Vlookup values.

Is there a way to solve this?

Your help is highly appreciated!

RMF