Re: Using INDEX and MATCH to find data in 2 different sheets
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 <950CC035-5237-4B63-8493-CADF04A97716@xxxxxxxxxxxxx>,
> RMF <RMF@xxxxxxxxxxxxxxxxxxxxxxxxx> 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
>
.
