I'm used to having a single value for the first term of INDEX, and you
have a range, so I'm not sure how that works. Is this an array
formula? If so, then the following may not apply.

I didn't see anybody suggest INDIRECT. MATCH wants the range itself,
and not the range address which is text. So put the complex range
address in a cell and refer to it with INDIRECT.

For example, in cell Z1, put
'[Master Material Pricing.xls]Master Material'!$C$3:$C$677&'[Master
Material Pricing.xls]Master Material'!$E$3:$E$677&'[Master Material
Pricing.xls]Master Material'!$G$3:$G$677

You may need to format Z1 to text or precede the string with another
apostrophe to keep the first apostrophe from being swallowed.

Your formula becomes
=IF($A$9,INDEX('[Master Material Pricing.xls]Master Material'!$K$3:$K
$677,MATCH(I36&$U$17&$J38,INDIRECT(Z1),0)),IF($A$1 1,INDEX('[Master
Material Pricing.xls]Master Material'!$L$3:$L$677,MATCH(I36&$U$17&
$J38,INDIRECT(Z1),0)),IF($A$13,INDEX('[Master Material
Pricing.xls]Master Material'!$M$3:$M$677,MATCH(I36&$U$17&
$J38,INDIRECT(Z1),0)),"")))

The logical test term of your IF statements are absolute $A$9 rather
than relative A9, and the lookup values of your MATCH statements are
mixed I36, $U$17, and $J38, so it is difficult to tell how you cause
the formula to repeat. It won't copy down a column very well. Like
some of the other comments suggest, use a helper column with the MATCH
lookup value. For example, if Z36 has =I36&$U$17&$J38, your formula
becomes

=IF($A$9,INDEX('[Master Material Pricing.xls]Master Material'!$K$3:$K
$677,MATCH(Z36,INDIRECT(Z1),0)),IF($A$11,INDEX('[Master Material
Pricing.xls]Master Material'!$L$3:$L$677,MATCH(Z36,INDIRECT(Z1),
0)),IF($A$13,INDEX('[Master Material Pricing.xls]Master Material'!$M
$3:$M$677,MATCH(Z36,INDIRECT(Z1),0)),"")))

HTH
Carl.

On Mar 24, 1:38 am, "Bernd" wrote:
Hello,

You use the formula part

MATCH(I36&$U$17&$J38,'[Master Material Pricing.xls]Master Material'!$C
$3:$C$677&'[Master Material Pricing.xls]Master Material'!$E$3:$E
$677&'[Master Material Pricing.xls]Master Material'!$G$3:$G$677,0)

THREE times in your big formula.

Move this part into another cell, say Z7 and substitute the 3
instances in your original formula by Z7.

If you want to simplify your formula further, get the other parts
INDEX(...,MATCH()) out into helper cells, too. With a little cost of
additional helper cells you will be rewarded by a huge gain in better
overview/simplicity.

Regards,
Bernd