Can anyone see a syntax error in this formula??

[leopardhawk]

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. ?

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

 

[JackDanIce]

You have more closing brackets than opening brackets. Try just this:

=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:

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

 

[leopardhawk]

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...

=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
	A	B	C
1	Federal Programs	Maximum Monthly Amounts	Thresholds
2	CPP	$1,175.83
3	YMPE		$58,700.00
4	OAS	$613.53
5	Repayment		$79,054.00
6	GIS
7	Single	$916.38	$18,600.00
8	Married	$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...

=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]

Is the table called Table_4? rather than Table4 or something else.

 

[leopardhawk]

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]

In that case make sure there are no leading/trailing spaces in the cell with CPP

 

[leopardhawk]

Bingo, trailing zero. Invisible of course. Thanks for everything!

 

[Fluff]

You're welcome & thanks for the feedback