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.
 

Excel Facts

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.

JackDanIce

Well-known Member
Joined
Feb 3, 2010
Messages
9,744
Office Version
  1. 365
Platform
  1. Windows
If this is a continuation relating to your previous post, I think you're making this much much harder for yourself when it comes to debugging.

A suggestion is to create a table with each column representing a different calculation and then link your paragraph to this table.

E.g. in a random sheet (named "Concat"), in cell A1 enter:
=IF(personal_info!C11>0,Personal_Info!C11,"Spouse/Partner")

Then your formula reduces to:
="It is estimated that " & Concat!A1 & "will receive a CPP pension of "

Instead of
="It is estimated that "&IF(personal_info!C11=0,"Spouse/Partner",personal_info!C11)&" will receive a CPP pension of "
(I just grabbed a random line near the bottom, reading from the top, 2 lines in and gave up)

Advantage: you just need to review this table for formula errors, without needing to isolate errors and miscalcs in such a long formula.

In Concat!B1 you can have ="It is estimated that " & A1 & "will receive a CPP pension of "
And then link column B together for your paragraph, e.g. = Concat!B1 & Concat!B2 etc.

KIS - Keep It Simple, you can add complexity afterwards, don't start complicated and then reverse engineer.


Finally,
In a blank sheet enter:
[A1] =CONCATENATE("A", "B")
[A2] = "A" &"B"

The results should be the same, i.e. you do not have to use the function CONCATENATE to coerce strings together.
 
Last edited:

leopardhawk

Well-known Member
Joined
May 31, 2007
Messages
587
Office Version
  1. 2016
Platform
  1. Windows
Hey Jack, thank you so much for your reply. At this point, as complicated as my formula is (or seems to be), it works well for what I need it to do and I don't want to reinvent it to resolve this issue which basically comes down to an INDEX/MATCH problem that is embedded within the CONCATENATE formula. As I iterated previously, the INDEX/MATCH I am trying to use in this formula works elsewhere in the worksheet so why it won't work within this formula is the mystery.

If we focus on the first INDEX/MATCH only, let's say I copy it and run it on its own in a separate cell. Below are the data ranges and in the first one, you can see that cell D36 contains an amount of $1227.32. Below that is a grab from worksheet CPP_Rate_Table_2 where cell B56 contains the data we need. So, the first formula works and returns 104% (the correct result). Below that you can see the formula and the results:
Book1
BCDE
35Start receiving at60 years of age65 years of age70 years of age
36CPP $785.48 $1,227.32 $1,742.79
37OAS $- $651.20 $885.63
38Total $785.48 $1,878.52 $2,628.43
39Difference$-441.84 $749.91
cpp&oas

Book1
ABCDEFGHI
562020$1,175.83 $1,387.66 $505.79 $638.28 $705.50 $197.34 $255.03 $2,500
cpp_rate_table2_web

Book1
B
53
54104%
55
cpp&oas
Cell Formulas
RangeFormula
B54B54=SUM(D36/INDEX(CPP_Rate_Table_2[Max Retirem''t],MATCH(YEAR(TODAY()),CPP_Rate_Table_2[Year],0)),"0%")

So all I'm doing is trying to point the new formula to a table (Table_4) on Sheet 11 that has the exact same amount ($1175.83) in it and the question remains, "why does the formula above work but the formula below returns the #VALUE! error?" I have also included the data range for the new INDEX/MATCH formula so you can see that they are valid. The table on Sheet 11 is named Table_4.
Book1
B
53
54#VALUE!
55
cpp&oas
Cell Formulas
RangeFormula
B54B54=SUM(D36/INDEX(Table_4[Maximum Monthly Amounts],MATCH("CPP",Table_4[Federal Programs],0)),"0%")

Book1
ABC
1Federal ProgramsMaximum Monthly AmountsThresholds
2CPP$1,175.83
3YMPE$58,700.00
4OAS613.53
5OAS Repayment$79,054.00
6GIS
7Single916.38$18,600.00
8Married551.63$24,576.00
Sheet11

Thanks again for your efforts... If you can help, great, if not, I will keep banging my head... :)
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
56,883
Office Version
  1. 365
Platform
  1. Windows
The CPP value in col B looks to be text not a number
 

leopardhawk

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

ADVERTISEMENT

Hey Fluff, thanks for jumping in. As mentioned, the exact same INDEX/MATCH formula is used embedded in another CONCATENATE formula on the same worksheet and it returns the amount of $1175.83. Not sure how that can work if it was text...
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
56,883
Office Version
  1. 365
Platform
  1. Windows
It's looking at a different table, which maybe why it works.
 

leopardhawk

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

ADVERTISEMENT

?? I only have one Table_4.
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
56,883
Office Version
  1. 365
Platform
  1. Windows
You 1st formula is looking at a table called CPP_Rate_Table_2 not table 4.
 

leopardhawk

Well-known Member
Joined
May 31, 2007
Messages
587
Office Version
  1. 2016
Platform
  1. Windows
Ahhh, I see what you are referring to. CPP_Rate_Table_2 is just the name of another Worksheet, It's not an actual Excel table. I originally gave it that name because the data was being taken from a website where it was stored in an HTML table. I am trying to get away from using the 'Get External Data'.

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

JackDanIce

Well-known Member
Joined
Feb 3, 2010
Messages
9,744
Office Version
  1. 365
Platform
  1. Windows
Hi @leopardhawk in response to thread #3

How much progress are you making to solve this or will a change in approach resolve faster?

Even if you stick at what you're doing, you're waiting on others to read, decipher and suggest changes from a sheet you built and understand and suggest correction.

From above, @Fluff noticed the difference in table names, which you hadn't picked up on, how many undiscovered issues are left to fix? Unlike me, he did read all the formula and code, to spot this!

With best of intentions, I think you should consider a different approach but if you're 2% away from finishing, go for it..

Maybe get a crash helmet guard for your head whilst waiting for the next reply to fix your problem? ;)
 

Watch MrExcel Video

Forum statistics

Threads
1,130,174
Messages
5,640,593
Members
417,154
Latest member
gm_jagath

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