Can anyone see a syntax error in this formula??

leopardhawk

Well-known Member
Joined
May 31, 2007
Messages
587
Office Version
  1. 2016
Platform
  1. Windows
This formula is giving me the dreaded #N/A error.

Can't see why...there is a legitimate dollar amount in the designated cell of Table 4. 😒

Code:
=INDEX(Table_4[Maximum Monthly Amounts],MATCH("CPP",Table_4[Federal Programs],0)),"$#,##0.00")
 

Excel Facts

How to show all formulas in Excel?
Press Ctrl+` to show all formulas. Press it again to toggle back to numbers. The grave accent is often under the tilde on US keyboards.

JackDanIce

Well-known Member
Joined
Feb 3, 2010
Messages
9,744
Office Version
  1. 365
Platform
  1. Windows
You have more closing brackets than opening brackets. Try just this:
Code:
=INDEX(Table_4[Maximum Monthly Amounts],MATCH("CPP",Table_4[Federal Programs],0))

If that works, wrap TEXT function around it with your formatting as needed:
Code:
=TEXT(INDEX(Table_4[Maximum Monthly Amounts],MATCH("CPP",Table_4[Federal Programs],0)), "#$#,##0.00")
 

leopardhawk

Well-known Member
Joined
May 31, 2007
Messages
587
Office Version
  1. 2016
Platform
  1. Windows
Thanks Jack, I actually stripped that formula out of a CONCATENATE formula. To slightly start over, here is a working formula that returns a perfect text sentence with the correct amount in it...
Code:
=CONCATENATE("For "&YEAR(TODAY())&" the maximum CPP monthly payment is "&TEXT(INDEX(CPP_Rate_Table_2[Max Retirem''t],MATCH(YEAR(TODAY()),CPP_Rate_Table_2[Year],0)),"$#,##0.00")&".
All I'm trying to do is point the formula to a different cell on a different worksheet (see below).
Book1
ABC
1Federal ProgramsMaximum Monthly AmountsThresholds
2CPP$1,175.83
3YMPE$58,700.00
4OAS$613.53
5Repayment$79,054.00
6GIS
7Single$916.38$18,600.00
8Married$551.63$24,576.00
Sheet11

Here is the formula that DOESN'T work and I can't figure out why? They are identical except for now pointing to a different sheet...
Code:
=CONCATENATE("For "&YEAR(TODAY())&" the maximum CPP monthly payment is "&TEXT(INDEX(Table_4[Maximum Monthly Amounts],MATCH("CPP",Table_4[Federal Programs],0)),"$#,##0.00")&".
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
56,915
Office Version
  1. 365
Platform
  1. Windows
Is the table called Table_4? rather than Table4 or something else.
 

leopardhawk

Well-known Member
Joined
May 31, 2007
Messages
587
Office Version
  1. 2016
Platform
  1. Windows

ADVERTISEMENT

The table is called Table 4. Excel is adding the underscore but it works fine in other formulas. Here's another strange twist to this issue...

I changed the CPP to OAS, nothing else was changed and the formula returns $613.53...!!! No errors. I'm stumped...
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
56,915
Office Version
  1. 365
Platform
  1. Windows
In that case make sure there are no leading/trailing spaces in the cell with CPP
 

leopardhawk

Well-known Member
Joined
May 31, 2007
Messages
587
Office Version
  1. 2016
Platform
  1. Windows
Bingo, trailing zero. Invisible of course. Thanks for everything!
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
56,915
Office Version
  1. 365
Platform
  1. Windows
You're welcome & thanks for the feedback
 

Watch MrExcel Video

Forum statistics

Threads
1,130,240
Messages
5,641,037
Members
417,189
Latest member
koelleyath

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top