# Can anyone see a syntax error in this formula??

#### leopardhawk

##### Well-known Member
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
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
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
Is the table called Table_4? rather than Table4 or something else.

#### leopardhawk

##### Well-known Member

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
In that case make sure there are no leading/trailing spaces in the cell with CPP

#### leopardhawk

##### Well-known Member
Bingo, trailing zero. Invisible of course. Thanks for everything!

#### Fluff

##### MrExcel MVP, Moderator
You're welcome & thanks for the feedback

Replies
3
Views
162
Replies
12
Views
399
Replies
3
Views
81
Replies
26
Views
334
Replies
18
Views
249

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.

### Which adblocker are you using?

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

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