Index/Match issue

[leopardhawk]

Hello forum friends,

As is, the formula below returns a perfectly good paragraph into a merged cell on my worksheet. After I make the modification I need to, I am getting a #VALUE! error!!

I have removed the leading equal sign(=) so as not to present an actual formula here on the forum and to be able to highlight the specific area I am trying to change. Note below that the part of the formula that I am modifying has been highlighted in BOLD, ITALICS and UNDERLINED.

CONCATENATE("It is estimated that "&IF(personal_info!C9=0,"Name",personal_info!C9)&" will receive a CPP pension of "&TEXT(D36*12,"$#,##0.00")&" per year ("&TEXT(D36,"$#,##0.00")&" per month) in real dollars: assumes that "&IF(personal_info!F9="M","he",IF(personal_info!F9="F","she","they"))&" "&IF(personal_info!F9="M","begins taking his",IF(personal_info!F9="F","begins taking her","begin taking their"))&" CPP when "&IF(personal_info!F9="M","he is",IF(personal_info!F9="F","she is","they are"))&" 65 years of age.

",

""&IF(personal_info!C9=0,"Name",personal_info!C9)&"'s CPP pension is "&TEXT(D36/INDEX(CPP_Rate_Table_2[Max Retirem''t],MATCH(YEAR(TODAY()),CPP_Rate_Table_2[Year],0)),"0%")&" of the maximum amount "&IF(personal_info!F9="M","he",IF(personal_info!F9="F","she","they"))&" could receive (assuming that "&IF(personal_info!F9="M","he",IF(personal_info!F9="F","she","they"))&" made maximum contributions throughout "&IF(personal_info!F9="M","his",IF(personal_info!F9="F","her","their"))&" career and began receiving CPP at 65 years of age).

",

"It is estimated that "&IF(personal_info!C11=0,"Spouse/Partner",personal_info!C11)&" will receive a CPP pension of "&TEXT(I36*12,"$#,##0.00")&" per year ("&TEXT(I36,"$#,##0.00")&" per month) in real dollars: assumes that "&IF(personal_info!F11="M","he",IF(personal_info!F11="F","she","they"))&" "&IF(personal_info!F11="M","begins taking his",IF(personal_info!F11="F","begins taking her","begin taking their"))&" CPP when "&IF(personal_info!F11="M","he is",IF(personal_info!F11="F","she is","they are"))&" 65 years of age.

",

""&IF(personal_info!C11=0,"Spouse/Partner",personal_info!C11)&"'s CPP pension is "&TEXT(I36/INDEX(CPP_Rate_Table_2[Max Retirem''t],MATCH(YEAR(TODAY()),CPP_Rate_Table_2[Year],0)),"0%")&" of the maximum amount "&IF(personal_info!F11="M","he",IF(personal_info!F11="F","she","they"))&" could receive (assuming that "&IF(personal_info!F11="M","he",IF(personal_info!F11="F","she","they"))&" made maximum contributions throughout "&IF(personal_info!F11="M","his",IF(personal_info!F11="F","her","their"))&" career and began receiving CPP at 65 years of age).")

Okay, below is the modified formula. Note that I have ONLY changed the parts identified above. You should also know that the exact changes are used elsewhere on the worksheet and are working perfectly. However, when I change this formula, I am getting the #VALUE! error. I could sure use some help to figure out why...

=CONCATENATE("It is estimated that "&IF(personal_info!C9=0,"Name",personal_info!C9)&" will receive a CPP pension of "&TEXT(D36*12,"$#,##0.00")&" per year ("&TEXT(D36,"$#,##0.00")&" per month) in real dollars: assumes that "&IF(personal_info!F9="M","he",IF(personal_info!F9="F","she","they"))&" "&IF(personal_info!F9="M","begins taking his",IF(personal_info!F9="F","begins taking her","begin taking their"))&" CPP when "&IF(personal_info!F9="M","he is",IF(personal_info!F9="F","she is","they are"))&" 65 years of age.

",

""&IF(personal_info!C9=0,"Name",personal_info!C9)&"'s CPP pension is "&TEXT(D36/INDEX(Table_4[Maximum Monthly Amounts],MATCH("CPP",Table_4[Federal Programs],0)),"0%")&" of the maximum amount "&IF(personal_info!F9="M","he",IF(personal_info!F9="F","she","they"))&" could receive (assuming that "&IF(personal_info!F9="M","he",IF(personal_info!F9="F","she","they"))&" made maximum contributions throughout "&IF(personal_info!F9="M","his",IF(personal_info!F9="F","her","their"))&" career and began receiving CPP at 65 years of age).

",

"It is estimated that "&IF(personal_info!C11=0,"Spouse/Partner",personal_info!C11)&" will receive a CPP pension of "&TEXT(I36*12,"$#,##0.00")&" per year ("&TEXT(I36,"$#,##0.00")&" per month) in real dollars: assumes that "&IF(personal_info!F11="M","he",IF(personal_info!F11="F","she","they"))&" "&IF(personal_info!F11="M","begins taking his",IF(personal_info!F11="F","begins taking her","begin taking their"))&" CPP when "&IF(personal_info!F11="M","he is",IF(personal_info!F11="F","she is","they are"))&" 65 years of age.

",

""&IF(personal_info!C11=0,"Spouse/Partner",personal_info!C11)&"'s CPP pension is "&TEXT(I36/INDEX(Table_4[Maximum Monthly Amounts],MATCH("CPP",Table_4[Federal Programs],0)),"0%")&" of the maximum amount "&IF(personal_info!F11="M","he",IF(personal_info!F11="F","she","they"))&" could receive (assuming that "&IF(personal_info!F11="M","he",IF(personal_info!F11="F","she","they"))&" made maximum contributions throughout "&IF(personal_info!F11="M","his",IF(personal_info!F11="F","her","their"))&" career and began receiving CPP at 65 years of age).")

Any suggestions are much appreciated. I can provide more info if required.

 

[Fluff]

Also, that doesn't explain how my second formula, on its own, can work in one place and not another. Banging my head...!

Where it works is that looking to match "CPP"?

 

[leopardhawk]

I can't begin to tell you how much I appreciate both yours and Fluff's suggestions, it means a lot to me. However, to use a slightly different analogy, I am on the one-inch line of a football field and the goal is ever so close... Not sure if you saw my previous reply (#9) to Fluff but there are not two different tables, only one.

Thanks again!

 

[leopardhawk]

Yes, the formula is identical.

 

[Fluff]

That begs the question, Why do you need to run the exact same formula twice?

 

[leopardhawk]

In the first instance, it is returning a dollar amount.
In the second, it is returning a percentage.

 

[Fluff]

In that case if the dollar amount works, it's got something to do with converting it to a percentage.
If you use this formula & point it to each cell being being used in the % calculation do they all return true
=isnumber(a2)

 

[leopardhawk]

Greeting to Jack and Fluff, sorry for the late reply, I was hospitalized for banging my head on the wall (just kidding). In reality, I spent quite a bit of time trying to sort this issue out and I am happy to report that it appears the problem is resolved.

This will likely not matter much to you as Excel experts but here goes... the data in my table (Table_4) was loaded into my spreadsheet from a website using an Excel Query. The website was one that I had created using Weebly. After exhausting everything else I could think of within Excel, even deleting all the worksheets related to my queries and reloading them, I finally decided to try another web hosting service. I rebuilt the tables using Jimdo and wah-lah, everything's working!!! I can't imagine what was in the Weebly tables that was causing the issue in the first place. Jimdo is WAY easier to use anyway!

Many thanks to you guys for the efforts you put in, they are much appreciated.

Cheers

 

[Fluff]

Glad you've sorted it & thanks for the feedback

 

[JackDanIce]

Sounds like you've resolved the issue, well done!