index with match function -two columns and one row

[badmi]

Hi all,
i am new to this forum. I have tried functions index and match to fetch a value but could not get success.
my query is :
I have to match two columns and one row to fetch a value, but i am getting error. after using below function.
for example: A50, 3X1.5X10, Mys Max===>569
=INDEX($C$6:$N$26,MATCH(A29&B29,$A$6:$A$26&$B$6:$B$26,0),MATCH(C28,$C$5:$N$5,0))
Please guide me and where i am going wrong.

[TABLE="width: 1065"]
<tbody>[TR]
[TD]-[/TD]
[TD]-[/TD]
[TD]FxsMax[/TD]
[TD]FysMax[/TD]
[TD]Fzs Max[/TD]
[TD]Mxs Max[/TD]
[TD]Mys Max[/TD]
[TD]Mzs Max[/TD]
[TD]Fxd Max[/TD]
[TD]Fyd Max[/TD]
[TD]Fzd Max[/TD]
[TD]Mxd Max[/TD]
[TD]Myd Max[/TD]
[TD]Mzd Max[/TD]
[/TR]
[TR]
[TD]AA[/TD]
[TD]1.5X1X6[/TD]
[TD="align: right"]4671[/TD]
[TD="align: right"]3336[/TD]
[TD="align: right"]3336[/TD]
[TD="align: right"]976[/TD]
[TD="align: right"]230[/TD]
[TD="align: right"]230[/TD]
[TD="align: right"]3559[/TD]
[TD="align: right"]6005[/TD]
[TD="align: right"]13345[/TD]
[TD="align: right"]556[/TD]
[TD="align: right"]556[/TD]
[TD="align: right"]556[/TD]
[/TR]
[TR]
[TD]AB[/TD]
[TD]3X1.5X6[/TD]
[TD="align: right"]4671[/TD]
[TD="align: right"]5516[/TD]
[TD="align: right"]5560[/TD]
[TD="align: right"]1220[/TD]
[TD="align: right"]664[/TD]
[TD="align: right"]664[/TD]
[TD="align: right"]3559[/TD]
[TD="align: right"]6005[/TD]
[TD="align: right"]13345[/TD]
[TD="align: right"]678[/TD]
[TD="align: right"]746[/TD]
[TD="align: right"]691[/TD]
[/TR]
[TR]
[TD]A10[/TD]
[TD]3X2X6[/TD]
[TD="align: right"]4671[/TD]
[TD="align: right"]4671[/TD]
[TD="align: right"]4671[/TD]
[TD="align: right"]1220[/TD]
[TD="align: right"]298[/TD]
[TD="align: right"]298[/TD]
[TD="align: right"]3559[/TD]
[TD="align: right"]6005[/TD]
[TD="align: right"]13345[/TD]
[TD="align: right"]678[/TD]
[TD="align: right"]1356[/TD]
[TD="align: right"]691[/TD]
[/TR]
[TR]
[TD]AA[/TD]
[TD]1.5X1X8[/TD]
[TD="align: right"]4671[/TD]
[TD="align: right"]5382[/TD]
[TD="align: right"]5382[/TD]
[TD="align: right"]976[/TD]
[TD="align: right"]258[/TD]
[TD="align: right"]258[/TD]
[TD="align: right"]3559[/TD]
[TD="align: right"]6005[/TD]
[TD="align: right"]13345[/TD]
[TD="align: right"]488[/TD]
[TD="align: right"]488[/TD]
[TD="align: right"]488[/TD]
[/TR]
[TR]
[TD]---[/TD]
[TD]3X1.5X8a[/TD]
[TD="align: right"]4671[/TD]
[TD="align: right"]5516[/TD]
[TD="align: right"]5560[/TD]
[TD="align: right"]1220[/TD]
[TD="align: right"]664[/TD]
[TD="align: right"]664[/TD]
[TD="align: right"]3559[/TD]
[TD="align: right"]6005[/TD]
[TD="align: right"]13345[/TD]
[TD="align: right"]597[/TD]
[TD="align: right"]597[/TD]
[TD="align: right"]597[/TD]
[/TR]
[TR]
[TD]A50[/TD]
[TD]3X1.5X8[/TD]
[TD="align: right"]12010[/TD]
[TD="align: right"]6005[/TD]
[TD="align: right"]6672[/TD]
[TD="align: right"]1762[/TD]
[TD="align: right"]502[/TD]
[TD="align: right"]502[/TD]
[TD="align: right"]6227[/TD]
[TD="align: right"]6005[/TD]
[TD="align: right"]14457[/TD]
[TD="align: right"]624[/TD]
[TD="align: right"]624[/TD]
[TD="align: right"]624[/TD]
[/TR]
[TR]
[TD]A60[/TD]
[TD]3X2X8[/TD]
[TD="align: right"]12010[/TD]
[TD="align: right"]6005[/TD]
[TD="align: right"]6672[/TD]
[TD="align: right"]1762[/TD]
[TD="align: right"]813[/TD]
[TD="align: right"]813[/TD]
[TD="align: right"]6227[/TD]
[TD="align: right"]6005[/TD]
[TD="align: right"]14457[/TD]
[TD="align: right"]895[/TD]
[TD="align: right"]895[/TD]
[TD="align: right"]895[/TD]
[/TR]
[TR]
[TD]A70[/TD]
[TD]4X3X8[/TD]
[TD="align: right"]12010[/TD]
[TD="align: right"]6005[/TD]
[TD="align: right"]6672[/TD]
[TD="align: right"]1762[/TD]
[TD="align: right"]475[/TD]
[TD="align: right"]475[/TD]
[TD="align: right"]6227[/TD]
[TD="align: right"]6005[/TD]
[TD="align: right"]14457[/TD]
[TD="align: right"]1627[/TD]
[TD="align: right"]1979[/TD]
[TD="align: right"]935[/TD]
[/TR]
[TR]
[TD]A05[/TD]
[TD]2X1X10[/TD]
[TD="align: right"]10409[/TD]
[TD="align: right"]4270[/TD]
[TD="align: right"]4270[/TD]
[TD="align: right"]1722[/TD]
[TD="align: right"]298[/TD]
[TD="align: right"]298[/TD]
[TD="align: right"]6227[/TD]
[TD="align: right"]6005[/TD]
[TD="align: right"]14457[/TD]
[TD="align: right"]895[/TD]
[TD="align: right"]895[/TD]
[TD="align: right"]895[/TD]
[/TR]
[TR]
[TD]A50[/TD]
[TD]3X1.5X10[/TD]
[TD="align: right"]12010[/TD]
[TD="align: right"]6005[/TD]
[TD="align: right"]6672[/TD]
[TD="align: right"]1762[/TD]
[TD="align: right"]569[/TD]
[TD="align: right"]569[/TD]
[TD="align: right"]6227[/TD]
[TD="align: right"]6005[/TD]
[TD="align: right"]14457[/TD]
[TD="align: right"]502[/TD]
[TD="align: right"]502[/TD]
[TD="align: right"]502[/TD]
[/TR]
[TR]
[TD]A60[/TD]
[TD]3X2X10[/TD]
[TD="align: right"]12010[/TD]
[TD="align: right"]6005[/TD]
[TD="align: right"]6583[/TD]
[TD="align: right"]1762[/TD]
[TD="align: right"]420[/TD]
[TD="align: right"]420[/TD]
[TD="align: right"]6227[/TD]
[TD="align: right"]6005[/TD]
[TD="align: right"]14457[/TD]
[TD="align: right"]759[/TD]
[TD="align: right"]759[/TD]
[TD="align: right"]759[/TD]
[/TR]
[TR]
[TD]A70[/TD]
[TD]4X3X10[/TD]
[TD="align: right"]10231[/TD]
[TD="align: right"]6005[/TD]
[TD="align: right"]6672[/TD]
[TD="align: right"]1762[/TD]
[TD="align: right"]420[/TD]
[TD="align: right"]420[/TD]
[TD="align: right"]6227[/TD]
[TD="align: right"]6005[/TD]
[TD="align: right"]14457[/TD]
[TD="align: right"]1627[/TD]
[TD="align: right"]1979[/TD]
[TD="align: right"]935[/TD]
[/TR]
[TR]
[TD]A80[/TD]
[TD]6X4X10[/TD]
[TD="align: right"]12010[/TD]
[TD="align: right"]6005[/TD]
[TD="align: right"]6672[/TD]
[TD="align: right"]1762[/TD]
[TD="align: right"]1491[/TD]
[TD="align: right"]1491[/TD]
[TD="align: right"]6227[/TD]
[TD="align: right"]6005[/TD]
[TD="align: right"]14457[/TD]
[TD="align: right"]1627[/TD]
[TD="align: right"]2034[/TD]
[TD="align: right"]935[/TD]
[/TR]
[TR]
[TD]A20[/TD]
[TD]3X1.5X13[/TD]
[TD="align: right"]12010[/TD]
[TD="align: right"]6005[/TD]
[TD="align: right"]6672[/TD]
[TD="align: right"]1762[/TD]
[TD="align: right"]908[/TD]
[TD="align: right"]908[/TD]
[TD="align: right"]6227[/TD]
[TD="align: right"]6005[/TD]
[TD="align: right"]14457[/TD]
[TD="align: right"]719[/TD]
[TD="align: right"]719[/TD]
[TD="align: right"]719[/TD]
[/TR]
[TR]
[TD]A30[/TD]
[TD]3X2X13[/TD]
[TD="align: right"]8541[/TD]
[TD="align: right"]5471[/TD]
[TD="align: right"]5471[/TD]
[TD="align: right"]1762[/TD]
[TD="align: right"]475[/TD]
[TD="align: right"]475[/TD]
[TD="align: right"]6227[/TD]
[TD="align: right"]6005[/TD]
[TD="align: right"]14457[/TD]
[TD="align: right"]1627[/TD]
[TD="align: right"]1722[/TD]
[TD="align: right"]935[/TD]
[/TR]
[TR]
[TD]A40[/TD]
[TD]4X3X13[/TD]
[TD="align: right"]12010[/TD]
[TD="align: right"]6005[/TD]
[TD="align: right"]6672[/TD]
[TD="align: right"]1762[/TD]
[TD="align: right"]542[/TD]
[TD="align: right"]542[/TD]
[TD="align: right"]6227[/TD]
[TD="align: right"]6005[/TD]
[TD="align: right"]14457[/TD]
[TD="align: right"]1627[/TD]
[TD="align: right"]2034[/TD]
[TD="align: right"]935[/TD]
[/TR]
[TR]
[TD]A80[/TD]
[TD]6X4X13[/TD]
[TD="align: right"]12010[/TD]
[TD="align: right"]6005[/TD]
[TD="align: right"]6672[/TD]
[TD="align: right"]1762[/TD]
[TD="align: right"]1762[/TD]
[TD="align: right"]1491[/TD]
[TD="align: right"]6227[/TD]
[TD="align: right"]6005[/TD]
[TD="align: right"]14457[/TD]
[TD="align: right"]1627[/TD]
[TD="align: right"]2034[/TD]
[TD="align: right"]935[/TD]
[/TR]
[TR]
[TD]A90[/TD]
[TD]8X6X13[/TD]
[TD="align: right"]15569[/TD]
[TD="align: right"]14145[/TD]
[TD="align: right"]8896[/TD]
[TD="align: right"]2034[/TD]
[TD="align: right"]1586[/TD]
[TD="align: right"]1586[/TD]
[TD="align: right"]6672[/TD]
[TD="align: right"]13345[/TD]
[TD="align: right"]15569[/TD]
[TD="align: right"]1695[/TD]
[TD="align: right"]3850[/TD]
[TD="align: right"]3850[/TD]
[/TR]
[TR]
[TD]A100[/TD]
[TD]10X8X13[/TD]
[TD="align: right"]15569[/TD]
[TD="align: right"]14145[/TD]
[TD="align: right"]8896[/TD]
[TD="align: right"]2034[/TD]
[TD="align: right"]2712[/TD]
[TD="align: right"]2915[/TD]
[TD="align: right"]6672[/TD]
[TD="align: right"]13345[/TD]
[TD="align: right"]15569[/TD]
[TD="align: right"]1695[/TD]
[TD="align: right"]3850[/TD]
[TD="align: right"]3850[/TD]
[/TR]
[TR]
[TD]A110[/TD]
[TD]8X6X15[/TD]
[TD="align: right"]15569[/TD]
[TD="align: right"]14145[/TD]
[TD="align: right"]8896[/TD]
[TD="align: right"]2034[/TD]
[TD="align: right"]2007[/TD]
[TD="align: right"]2007[/TD]
[TD="align: right"]6672[/TD]
[TD="align: right"]13345[/TD]
[TD="align: right"]15569[/TD]
[TD="align: right"]1695[/TD]
[TD="align: right"]3850[/TD]
[TD="align: right"]3850[/TD]
[/TR]
[TR]
[TD]A120[/TD]
[TD]10X8X15[/TD]
[TD="align: right"]15569[/TD]
[TD="align: right"]14145[/TD]
[TD="align: right"]8896[/TD]
[TD="align: right"]2034[/TD]
[TD="align: right"]1532[/TD]
[TD="align: right"]1532[/TD]
[TD="align: right"]6672[/TD]
[TD="align: right"]13345[/TD]
[TD="align: right"]15569[/TD]
[TD="align: right"]1695[/TD]
[TD="align: right"]3850[/TD]
[TD="align: right"]3850[/TD]
[/TR]
</tbody>[/TABLE]


Regards,
Badmi

 

[Fluff]

Hi & welcome to MrExcel.

Did you confirm the formula with Ctrl Shift Enter, rather then just Enter?
If so you will see the formula wrapped in {}

 

[badmi]

Thanks for welcoming,

Sir,

I did try Ctrl+Shift+Enter. i didnt get fruitfull result.
Values are not changing and it is not working for other parameters.

 

[Fluff]

Are the values you are trying to match in A29 & B29 with the header to match in C28?

 

[badmi]

i didnt get your point. But i can give u an examples:

[TABLE="class: cms_table, width: 1065"]
<tbody>[TR]
[TD]AB,3X1.5X6,FysMax===>5516

A80,6X4X13,FzsMax===>6672

I need to fetch values in Bold from table.[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[Fluff]

Your formula is looking at the green cells to get the lookup values

Excel 2013/2016
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
5	-	-	FxsMax	FysMax	Fzs Max	Mxs Max	Mys Max	Mzs Max	Fxd Max	Fyd Max	Fzd Max	Mxd Max	Myd Max	Mzd Max
6	AA	1.5X1X6	4671	3336	3336	976	230	230	3559	6005	13345	556	556	556
7	AB	3X1.5X6	4671	5516	5560	1220	664	664	3559	6005	13345	678	746	691
8	A10	3X2X6	4671	4671	4671	1220	298	298	3559	6005	13345	678	1356	691
9	AA	1.5X1X8	4671	5382	5382	976	258	258	3559	6005	13345	488	488	488
10	---	3X1.5X8a	4671	5516	5560	1220	664	664	3559	6005	13345	597	597	597
11	A50	3X1.5X8	12010	6005	6672	1762	502	502	6227	6005	14457	624	624	624
12	A60	3X2X8	12010	6005	6672	1762	813	813	6227	6005	14457	895	895	895
13	A70	4X3X8	12010	6005	6672	1762	475	475	6227	6005	14457	1627	1979	935
14	A05	2X1X10	10409	4270	4270	1722	298	298	6227	6005	14457	895	895	895
15	A50	3X1.5X10	12010	6005	6672	1762	569	569	6227	6005	14457	502	502	502
16	A60	3X2X10	12010	6005	6583	1762	420	420	6227	6005	14457	759	759	759
17	A70	4X3X10	10231	6005	6672	1762	420	420	6227	6005	14457	1627	1979	935
18	A80	6X4X10	12010	6005	6672	1762	1491	1491	6227	6005	14457	1627	2034	935
19	A20	3X1.5X13	12010	6005	6672	1762	908	908	6227	6005	14457	719	719	719
20	A30	3X2X13	8541	5471	5471	1762	475	475	6227	6005	14457	1627	1722	935
21	A40	4X3X13	12010	6005	6672	1762	542	542	6227	6005	14457	1627	2034	935
22	A80	6X4X13	12010	6005	6672	1762	1762	1491	6227	6005	14457	1627	2034	935
23	A90	8X6X13	15569	14145	8896	2034	1586	1586	6672	13345	15569	1695	3850	3850
24	A100	10X8X13	15569	14145	8896	2034	2712	2915	6672	13345	15569	1695	3850	3850
25	A110	8X6X15	15569	14145	8896	2034	2007	2007	6672	13345	15569	1695	3850	3850
26	A120	10X8X15	15569	14145	8896	2034	1532	1532	6672	13345	15569	1695	3850	3850
27
28			Mys Max
29	A50	3X1.5X10			569
Data

Cell Formulas
Range	Formula
E29	{=INDEX($C$6:$N$26,MATCH(A29&B29,$A$6:$A$26&$B$6:$B$26,0),MATCH(C28,$C$5:$N$5,0))}
Press CTRL+SHIFT+ENTER to enter array formulas.

Is that were your values are?

 

[badmi]

Thank you alot.
with your help i got results.
can you Pls tell me. what went wrong in my array formula?

So that i should not repeat same mistake.

 

[Fluff]

There is nothing wrong with your formula.

 

[bosco_yip]

Or try the Sumproduct function without array enter.

=SUMPRODUCT((A29=$A$6:$A$26)*($B29=B$6:$B$26)*($C$5:$N$5=C28),$C$6:$N$26)

Regards
Bosco