Index match

[Zubair]

Hi

What mistake I am doing to get figure 150 in cell G2? Please help

formula I am applying

=INDEX($B$2:$D$5,MATCH(1,($A$2:$A$5=$F2)),MATCH(G1,$B$1:$D$1,0))

Size	Alpha Kraft	Box Board	Delta			Alpha Kraft
20​	150​	0​	0​		20​	#N/A​
21​	0​	5​	0​
22​	0​	0​	15​
23​	0​	0​	0​

 

[etaf]

try
=INDEX(B3:D6,MATCH(F3,A3:A6,0),MATCH(G2,B2:D2,0))

I'm assuming the numbers are formatted as numbers , maybe changed to text when I copied to excle

Book11
	A	B	C	D	E	F	G
1
2	Size	Alpha Kraft	Box Board	Delta			Alpha Kraft
3	20	150	0	0		20	150
4	21	7	5	0		21	7
5	22	8	0	15		23	9
6	23	9	0	0			#N/A
7							#N/A
8
Sheet1

Cell Formulas
Range	Formula
G3:G7	G3	=INDEX($B$3:$D$6,MATCH(F3,$A$3:$A$6,0),MATCH(G$2,$B$2:$D$2,0))

 

[Peter_SSs]

What mistake I am doing to get figure 150 in cell G2? Please help

formula I am applying

=INDEX($B$2:$D$5,MATCH(1,($A$2:$A$5=$F2)),MATCH(G1,$B$1:$D$1,0))

To answer your question: The mistake(s) are in the blue section

• ($A$2:$A$5=$F2) produces an array of TRUE/FALSE values yet you are trying to match a "1". You need to either
  - Change the "1" to TRUE, or
  - coerce the TRUE/FALSE array into 1's and 0's as shown below
  
  
• In any case that blue MATCH is not looking for an exact match like your final MATCH is since you are missing the third argument of zero
  
  
• Finally, I think in your Excel version that formula would need to be confirmed with Ctrl+Shift+Enter, not just Enter

So, you could have done it like this, but I think that @etaf's approach is better and would make your two MATCH functions similar.

22 02 17.xlsm
	A	B	C	D	E	F	G
1	Size	Alpha Kraft	Box Board	Delta			Alpha Kraft
2	20	150	0	0		20	150
3	21	7	5	0
4	22	8	0	15
5	23	9	0	0
Index match

Cell Formulas
Range	Formula
G2	G2	=INDEX($B$2:$D$5,MATCH(1,--($A$2:$A$5=$F2),0),MATCH(G1,$B$1:$D$1,0))
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[Zubair]

Thanks, Peter