Excellent, many thanks!

One question regarding this formula:

=INDEX($B$4:$D$6,MATCH(D$8,$A$4:$A$6,0),MATCH(1,($ B$1:$D$1=$A9)*($B$2:$D$2=$B9)*($B$3:$D$3=$C9),0))

Why is the lookup value in the second MATCH formula 1? Dont understand this
one, perhaps I am missing something here

Thnks!

RMF

==========
"Domenic" wrote:

Try the following formulas which need to be confirmed with
CONTROL+SHIFT+ENTER, not just ENTER...

D9, copied down:

=INDEX($B$4:$D$4,MATCH(1,($B$1:$D$1=$A9)*($B$2:$D$ 2=$B9)*($B$3:$D$3=$C9),
0))

E9, copied down:

=INDEX($B$5:$D$5,MATCH(1,($B$1:$D$1=$A9)*($B$2:$D$ 2=$B9)*($B$3:$D$3=$C9),
0))

F9, copied down:

=INDEX($B$6:$D$6,MATCH(1,($B$1:$D$1=$A9)*($B$2:$D$ 2=$B9)*($B$3:$D$3=$C9),
0))

Or, it can be done using one formula...

D9, copied down and across:

=INDEX($B$4:$D$6,MATCH(D$8,$A$4:$A$6,0),MATCH(1,($ B$1:$D$1=$A9)*($B$2:$D$
2=$B9)*($B$3:$D$3=$C9),0))

Hope this helps!

In article ,
RMF wrote:

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