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