Looking for value

[XcelLearner]

Hi,

I have a value looking problem that is out of my depth, and would appreciate your help.

I have a data set in array L5:O28 as in the photo, with the return of each month. Investment B1 was first made in 6/30/2021 and therefore only captured the return since July 2021. I will have investment B2, B3, and so on as the array will expand, but let's keep it at L5:O28 at the moment.

I have a desired result array in Q5:T28 to report the monthly return of Investment B1. The month was recorded as 1 for Jan, 2 for Feb, etc. Any date before the Investment was made in 6/30/2021 should be returned as a blank like in the photo. What is the formula for that (cell S6), so that if I change the Investment to B2, it will change as well?

I use Office 365.

Thank you very much.

 

[ACommandLineKindaGuy]

I'm not quite clear on what you're asking. I get that you want Q5:T17 to reflect the investment specified in S4. What's confusing me is that Columns M and N are devoted to investment B1, but you have a single column O for investment B2. Don't you need for either investment a column for "return" and one for the investment value? Where are those two columns located for investment B2?

 

[XcelLearner]

I'm not quite clear on what you're asking. I get that you want Q5:T17 to reflect the investment specified in S4. What's confusing me is that Columns M and N are devoted to investment B1, but you have a single column O for investment B2. Don't you need for either investment a column for "return" and one for the investment value? Where are those two columns located for investment B2?

Thank you very much for your question. B1 and B2 are in a pool of investments, so as long as B2 is invested, they will share the same return. For example, if I made another investment in October 2021, the value of the investment will be reported in array O15:O31.

I revised my data table to add B2 values and transposed the desired table from Q5-T17 into Q5-AC9 as it allows better expansion.

 

[Peter_SSs]

It would be very helpful, and get you faster responses, if you could provide the sample data in a form that we can copy/paste to test with. See XL2BB

See if this does what you want

23 02 12.xlsm
	L	M	N	O	P	Q	R	S	T
4								B1
5			B1	B2	B3	Month	2020	2021	2022
6	31/01/2021	-1%				1			4%
7	28/02/2021	0%				2			9%
8	31/03/2021	-1%				3			-5%
9	30/04/2021	4%				4			-5%
10	31/05/2021	11%				5			13%
11	30/06/2021	7%	100			6		7%	3%
12	31/07/2021	-3%	101			7		-3%	-5%
13	31/08/2021	-1%	102			8		-1%	3%
14	30/09/2021	-1%	103	200		9		-1%	7%
15	31/10/2021	4%	104	201		10		4%
16	30/11/2021	8%	105	202		11		8%
17	31/12/2021	11%	106	203		12		11%
18	31/01/2022	4%	107	204
19	28/02/2022	9%	108	205
20	31/03/2022	-5%	109	206	305
21	30/04/2022	-5%	110	207	306
22	31/05/2022	13%	111	208	307
23	30/06/2022	3%	112	209	308
24	31/07/2022	-5%	113	210	309
25	31/08/2022	3%	114	211	310
26	30/09/2022	7%	115	212	311
Investment

Cell Formulas
Range	Formula
R6:T17	R6	=LET(rw,MATCH(EOMONTH(DATE(R$5,$Q6,1),0),$L$6:$L$26,0),v,IFNA(INDEX($N$6:$P$26,rw,MATCH($S$4,$N$5:$P$5,0)),""),IF(v="","",INDEX($M$6:$M$26,rw)))

If I change S4 to B3:

23 02 12.xlsm
	L	M	N	O	P	Q	R	S	T
4								B3
5			B1	B2	B3	Month	2020	2021	2022
6	31/01/2021	-1%				1
7	28/02/2021	0%				2
8	31/03/2021	-1%				3			-5%
9	30/04/2021	4%				4			-5%
10	31/05/2021	11%				5			13%
11	30/06/2021	7%	100			6			3%
12	31/07/2021	-3%	101			7			-5%
13	31/08/2021	-1%	102			8			3%
14	30/09/2021	-1%	103	200		9			7%
15	31/10/2021	4%	104	201		10
16	30/11/2021	8%	105	202		11
17	31/12/2021	11%	106	203		12
18	31/01/2022	4%	107	204
19	28/02/2022	9%	108	205
20	31/03/2022	-5%	109	206	305
21	30/04/2022	-5%	110	207	306
22	31/05/2022	13%	111	208	307
23	30/06/2022	3%	112	209	308
24	31/07/2022	-5%	113	210	309
25	31/08/2022	3%	114	211	310
26	30/09/2022	7%	115	212	311
Investment

Cell Formulas
Range	Formula
R6:T17	R6	=LET(rw,MATCH(EOMONTH(DATE(R$5,$Q6,1),0),$L$6:$L$26,0),v,IFNA(INDEX($N$6:$P$26,rw,MATCH($S$4,$N$5:$P$5,0)),""),IF(v="","",INDEX($M$6:$M$26,rw)))

 

[XcelLearner]

Hi Peter,

Thank you very much for your help. Yes, I installed the Add-in as suggested, and will use it next time.
I would like to have a few tweaks if you could help:

1. In your first example with B1, the first value of it in column N is in June 2021, so the desired result will show first return in July 2021 of -3% (and June 2021 of being blank). The reason is: if I add new investment on 6/30/2021, the first month of having a return will be the next month, which is July. So basically, can you formula delay the result by a month?

2. Can you please transpose the result if possible? Rather the months are stored in rows, they are in columns, like this:

Always appreciate your kind help.
Best regards,

 

[Peter_SSs]

Try this

23 02 12.xlsm
	L	M	N	O	P	Q						W	X	Y	Z	AA	AB	AC	AD	AE	AF	AG	AH	AI
3												B1
4												Month	1	2	3	4	5	6	7	8	9	10	11	12
5			B1	B2	B3							2020
6	31/01/2021	-1%										2021							-3%	-1%	-1%	4%	8%	11%
7	28/02/2021	0%										2022	4%	9%	-5%	-5%	13%	3%	-5%	3%	7%
8	31/03/2021	-1%
9	30/04/2021	4%
10	31/05/2021	11%
11	30/06/2021	7%	100
12	31/07/2021	-3%	101
13	31/08/2021	-1%	102
14	30/09/2021	-1%	103	200
15	31/10/2021	4%	104	201
16	30/11/2021	8%	105	202
17	31/12/2021	11%	106	203
18	31/01/2022	4%	107	204
19	28/02/2022	9%	108	205
20	31/03/2022	-5%	109	206	305
21	30/04/2022	-5%	110	207	306
22	31/05/2022	13%	111	208	307
23	30/06/2022	3%	112	209	308
24	31/07/2022	-5%	113	210	309
25	31/08/2022	3%	114	211	310
26	30/09/2022	7%	115	212	311
Investment (2)

Cell Formulas
Range	Formula
X5:AI7	X5	=LET(eom,DATE($W5,X$4+1,0),rw,MATCH(eom,$L$6:$L$26,0),v,IF(eom=XLOOKUP(0,FILTER($N$6:$P$26,$N$5:$P$5=$W$3),$L$6:$L$26,"",1),"",IFNA(INDEX($N$6:$P$26,rw,MATCH($W$3,$N$5:$P$5,0)),"")),IF(v="","",INDEX($M$6:$M$26,rw)))

 

[XcelLearner]

That works beautifully. Thank you and have a great day, Peter.

 

[Peter_SSs]

You're welcome. Thanks for the follow-up.