# Formula result will not show when formula is copied to another cell

1. ## Formula result will not show when formula is copied to another cell

I had created this formula that worked fine:
=INDEX('COGS sorted '!\$F\$9:\$F\$249,MATCH(A47,IF('COGS sorted '!\$B\$9:\$B\$249=C47,'COGS sorted '!\$A\$9:\$A\$249),0))

I then added an ISNA formula to the above formula in cell L43 and the result is blank but should be 529.39. This is the formula:
IF(ISNA(INDEX('COGS sorted '!\$F\$10:\$F\$250,MATCH(A43,IF('COGS sorted '!\$B\$10:\$B\$250=C43,'COGS sorted '!\$A\$10:\$A\$250),0))),"",INDEX('COGS sorted '!\$F\$10:\$F\$250,MATCH(A43,IF('COGS sorted '!\$B\$10:\$B\$250=C43,'COGS sorted '!\$A\$10:\$A\$250),0)))

I know that Excel won't display a result if the formula contains over 255 characers, but my formula has only 236. Anybody know why the result is not being displayed?

Thanks!  Register To Reply

2. ## Re: Formula result will not show when formula is copied to another cell

Welcome to the forum. You have to enter that formula with Ctrl+Shift+Enter. When you do that you get 529.39  Register To Reply

3. ## Re: Formula result will not show when formula is copied to another cell

BTW - if you don't need this file to work in older Excel versions you can use:

=IFERROR(INDEX('COGS sorted '!\$F\$10:\$F\$250,MATCH(A43,IF('COGS sorted '!\$B\$10:\$B\$250=C43,'COGS sorted '!\$A\$10:\$A\$250),0)),"")

also needs to be confirmed with C-S-E  Register To Reply

4. ## Re: Formula result will not show when formula is copied to another cell

Thanks, I can't believe I forgot that!  Register To Reply

5. ## Re: Formula result will not show when formula is copied to another cell

This will shorten the formula. Thanks!  Register To Reply

6. ## Re: Formula result will not show when formula is copied to another cell

You're welcome. Thanks for the 'star tap'.   Register To Reply