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.

 

[JackDanIce]

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.

 

[leopardhawk]

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
	B	C	D	E
35	Start receiving at	60 years of age	65 years of age	70 years of age
36	CPP	$785.48	$1,227.32	$1,742.79
37	OAS	$-	$651.20	$885.63
38	Total	$785.48	$1,878.52	$2,628.43
39	Difference	$-441.84		$749.91
cpp&oas

Book1
	A	B	C	D	E	F	G	H	I
56	2020	$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
54	104%
55
cpp&oas

Cell Formulas
Range	Formula
B54	B54	=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
Range	Formula
B54	B54	=SUM(D36/INDEX(Table_4[Maximum Monthly Amounts],MATCH("CPP",Table_4[Federal Programs],0)),"0%")

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	OAS Repayment		$79,054.00
6	GIS
7	Single	916.38	$18,600.00
8	Married	551.63	$24,576.00
Sheet11

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

 

[Fluff]

The CPP value in col B looks to be text not a number

 

[leopardhawk]

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]

It's looking at a different table, which maybe why it works.

 

[leopardhawk]

?? I only have one Table_4.

 

[Fluff]

You 1st formula is looking at a table called CPP_Rate_Table_2 not table 4.

 

[leopardhawk]

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]

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?