VLOOKUP - Multiple cells in lookup value

[Guest]

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!A15,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

 

[Bob Phillips]

=INDEX(D15,MATCH(A1&A2&A3,A1:A5&B1:B5&C1:C5,0))

which is an array formula, it should be committed with Ctrl-Shift-Enter, not
just Enter.

--

HTH

Bob Phillips

(remove nothere from the email address if mailing direct)