Re: Using INDEX and MATCH to find data in 2 different sheets

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

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
> 
.

Relevant Pages

  • Re: Binding Example
    ... I dont use a datatable. ... I must be missing some fundamental "key" in the relation with the Dataset and a Datatable. ... 'Not binding - just setting value ... its not really binding the value - just displaying it in the textbox. ...
    (microsoft.public.dotnet.languages.vb)
  • Re: What Script Does This SIte Use?
    ... I mean PHP code. ... have lot of feature yet, missing some stuff, but taht doesn't mean if the ... >> create a site like this, better create from scratch, dont copy this one. ... >> Anyway that's my first review of client side coding, ...
    (alt.php)
  • Re: What Script Does This SIte Use?
    ... I mean PHP code. ... have lot of feature yet, missing some stuff, but taht doesn't mean if the ... >> create a site like this, better create from scratch, dont copy this one. ... >> Anyway that's my first review of client side coding, ...
    (comp.lang.php)
  • FS Williams Funhouse 1000.00
    ... Playfield, I dont see any ... May be missing a link or two there. ... CPU is intact, but driver board and sound ...
    (rec.games.pinball)
  • Re: What kind of optos in DW Mini-plyfield?
    ... boards mentality. ... if they were missing i guess i might.. ... i think i spent like $5 at future active electronics, ... you dont want light in there and you dont want to be hitting one ...
    (rec.games.pinball)