Index/Match issue

leopardhawk

Well-known Member
Joined
May 31, 2007
Messages
587
Office Version
  1. 2016
Platform
  1. Windows
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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
56,711
Office Version
  1. 365
Platform
  1. Windows
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"?
 

Excel Facts

Can you sort left to right?
To sort left-to-right, use the Sort dialog box. Click Options. Choose "Sort left to right"

leopardhawk

Well-known Member
Joined
May 31, 2007
Messages
587
Office Version
  1. 2016
Platform
  1. Windows
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

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

ADVERTISEMENT

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

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
56,711
Office Version
  1. 365
Platform
  1. Windows
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

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

ADVERTISEMENT

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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
56,711
Office Version
  1. 365
Platform
  1. Windows
Glad you've sorted it & thanks for the feedback
 

JackDanIce

Well-known Member
Joined
Feb 3, 2010
Messages
9,744
Office Version
  1. 365
Platform
  1. Windows
Sounds like you've resolved the issue, well done!
 

Watch MrExcel Video

Forum statistics

Threads
1,129,876
Messages
5,638,788
Members
417,052
Latest member
Noobest

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