You are right. sorry. It got mixed up when pasting in from excel into a post.
The corrected version below:

A B C D E F
1 Red Orange Green
2 Blue Purple White
3 Yellow Black Pink
4 Small 10 11 5
5 Medium 12 7 4
6 Large 6 3 2
7
8 Small Medium Large
9 Red Blue Yellow #N/A
10 Orange Purple Black
11 Green White Pink

The formula I use in cell D9 is
{=INDEX(B4:D4;MATCH(A9&B9&C9;B1:B3&C1:C3&D1:D3;0))}

Thnks,

R









-----------------------------------------

"FiluDlidu" wrote:

> Just wondering how you can expect a value of 10 when 10 is not even part of
> your lookup array (which is C5:E5)?
>
> "RMF" wrote:
>
> > Dear Excel geniuses,
> >
> > I have two excel worksheets. One with data which is arranged in columns and
> > one where I want to pull the data into which should be arranged in rows. I
> > need to find a value based on 3 criteria and for that reason I use the INDEX
> > and MATCH formulas, however my excel knowledge abandons me here so I was
> > hoping for your assistance. In order to clarify I have made below example:
> >
> > A B C D E F
> > 1 Red Orange Green
> > 2 Blue Purple White
> > 3 Yellow Black Pink
> > 4 Small 10 11 5
> > 5 Medium 12 7 4
> > 6 Large 6 3 2
> > 7
> > 8 Small Medium Large
> > 9 Red Blue Yellow #N/A
> > 10 Orange Purple Black
> > 11 Green White Pink
> >
> > The formula I use in cell D9 is
> > {=INDEX(C5:E5;MATCH(B10&C10&D10;C2:C7&D2:D7&E2:E7;0))}
> >
> > The formula should return 10 but it gives a #N/A. Because of the lay-out of
> > the data I have, I am not very flexible with arranging the data differently
> > so I can use another solution.
> >
> > I have pasted the example straight out of Excel so I hope it is still
> > readible.
> >
> > Do you have any idea?
> >
> > Thnks!
> >
> > RMF