Using INDEX and MATCH to find data in 2 different sheets

[RMF]

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&D27&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

 

[FiluDlidu]

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]

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(B44;MATCH(A9&B9&C9;B1:B3&C1:C3&D13;0))}

Thnks,

R









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

 

[Domenic]

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!

 

[RMF]

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]

Excellent, many thanks!

You're very welcome! Glad I could help!

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

In the second MATCH function, you'll notice that there are three
conditional statements. Each statement returns an array of TRUE and/or
FALSE as each element in the array is evaluated...

($B$1:$D$1=$A9) evaluates to:

{TRUE,FALSE,FALSE}

($B$2:$D$2=$B9) evaluates to:

{TRUE,FALSE,FALSE}

($B$3:$D$3=$C9) evaluates to:

{TRUE,FALSE,FALSE}

These arrays are then multiplied together...

($B$1:$D$1=$A9)*($B$2:$D$2=$B9)*($B$3:$D$3=$C9) which evaluates to:

{TRUE,FALSE,FALSE}*{TRUE,FALSE,FALSE}*{TRUE,FALSE,FALSE} and gives us:

{1,0,0}

Notice that the numerical equivalent of TRUE/FALSE is 1/0, respectively.
Also, you'll notice that the statements evaluate to an array of 1's and
0's. With the lookup value for the MATCH function being 1, MATCH
returns 1,which is used as an argument for the INDEX function.

Hope this helps!