Formula add on new Function

[dorian25]

Hi all,

I trust that you are well.

My issue consists of not being not able to add to my initial formula with an if(vlookup) function (not sure why).

As you can see in column K4 the formula looks for the date, currency, Amount, Date USD, and Close to make an adjustment if there is a currency other than EUR. The issue is if column K (date) has not yet happened (e.g.) it makes the cell go empty but not "0" and this makes my other function on this column sum-product no workable. That's why I am trying to add this new function '=+IF(VLOOKUP(I15,D4:E2888,1,TRUE)=I15,"","0") to make it add a 0 if the column K and column D/F has not happened yet

Could you also help me do this formula for USD and GBP combined?

Formula USD: '=IF(K4="total:","0",IF(L4=0,"0",IFERROR(IF(J4="USD",L4/(IFERROR(VLOOKUP(K4,$D$4:$E$5002,2,FALSE),IFERROR(VLOOKUP(K4+1,$D$4:$E$5002,2,FALSE),VLOOKUP(K4+2,$D$4:$E$5002,2,FALSE)))),L4),"")))
Formula GBP: '=IF(K11="total:","0",IF(L11=0,"0",IFERROR(IF(J11="GBP",L11/(IFERROR(VLOOKUP(K11,$F$4:$G$5002,2,FALSE),IFERROR(VLOOKUP(K11+1,$F$4:$G$5002,2,FALSE),VLOOKUP(K11+2,$F$4:$G$5002,2,FALSE)))),L11),"")))
New Formula: '=+IF(VLOOKUP(I15,D4:E2888,1,TRUE)=I15,"","0")

Thank you so much in advance.

Kind regards,
Dorian

 

[ExceLoki]

did change a couple of the dates in the original post data to test each currency type.
try this:
---------------------

Book1
	D	E	F	G	H	I	J	K	L	M
2										Final
3	Date USD	Close	Date GBP	Close		Name	Currency	Date	Amount	Amount, EUR
4	1/2/2012	$ 1.29	1/2/2012	£ 0.83		Alto	EUR	1/9/2012	1,712,773.00	1,712,773.00
5	1/3/2012	$ 1.32	1/3/2012	£ 0.83		Alto	EUR	5/23/2018	42,200.00	-
6	1/4/2012	$ 1.29	1/4/2012	£ 0.83		Alto	EUR	12/14/2018	678,000.00	-
7	1/5/2012	$ 1.28	1/5/2012	£ 0.83		Alto	USD	1/9/2012	1,395,000.00	1,089,843.75
8	1/6/2012	$ 1.27	1/6/2012	£ 0.82		Alto	EUR	3/25/2024	1,411,000.00	-
9	1/9/2012	$ 1.28	1/9/2012	£ 0.83		Alto	EUR	12/18/2020	102,000.00	-
10	1/10/2012	$ 1.28	1/10/2012	£ 0.82		Alto	GBP	1/9/2012	1,154,000.00	26,000.00
11	1/11/2012	$ 1.27	1/11/2012	£ 0.83		Alto	GBP	3/24/2022	1,245,000.00	-
12	1/12/2012	$ 1.28	1/12/2012	£ 0.84		Alto	EUR	6/10/2022	510,000.00	-
13	1/13/2012	$ 1.27	1/13/2012	£ 0.83		Alto	EUR	7/8/2022	26,000.00	-
14	1/16/2012	$ 1.27	1/16/2012	£ 0.83		Alto	USD	10/21/2024	27,000.00	-
15	1/17/2012	$ 1.27	1/17/2012	£ 0.83		Alto	USD	10/30/2022	15,000.00	-
16	1/18/2012	$ 1.29	1/18/2012	£ 0.83
17	1/19/2012	$ 1.30	1/19/2012	£ 0.84
18	1/20/2012	$ 1.29	1/20/2012	£ 0.83
Sheet1 (2)

Cell Formulas
Range	Formula
M4:M15	M4	=IFERROR(IF(MATCH(K4,D:D,0)>0,IFS(K7="total",0,L4=0,0,J4="USD",IFERROR(IF(J4="USD",L4/(IFERROR(VLOOKUP(K4,D:E,2,FALSE),IFERROR(VLOOKUP(K4+1,D:E,2,FALSE),VLOOKUP(K4+2,D:E,2,FALSE)))),L4),""),J4="GBP",IFERROR(IF(J7="GBP",L7/(IFERROR(VLOOKUP(K7,F:G,2,FALSE),IFERROR(VLOOKUP(K7+1,F:G,2,FALSE),VLOOKUP(K7+2,F:G,2,FALSE)))),L7),""),J4="EUR",L4),0),0)

 

[dorian25]

Hi,

Thanks for the help.

But there are two issues with your formula.

1. if column K (date) appears on a weekend my formula would do a +1 or a +2 to get a match from columns D and F. You're for some reason does not...

2. In your formula if the date in column K does not exist in columns D and F it will return a blank. This is what trying to solve to get a 0. Because I am trying to make a sumproduct on columns K and M and unfortunately if it is only banc the formula does not work.

Thank you in advance.

Cheers,
Dorian

 

[Fluff]

How about

=IF(OR(K7="total",L4=0),0,IF(J4="USD",IFNA(L4/VLOOKUP(WORKDAY(K4-1,1),$D$4:$E$5002,2,0),0),IF(J4="GBP",IFNA(L4/VLOOKUP(WORKDAY(K4-1,1),$F$4:$G$5002,2,0),0),L4)))

 

[ExceLoki]

i updated my formula to correct the weekend dates, it will also show a 0 in the Amount EUR column if the date has not happened yet.
the data given does not have all the dates used. please try the below formula and let us know if it works for your needs
-----------------------

Formula add on new Function- currency convert by date.xlsx
	D	E	F	G	H	I	J	K	L	M	N	O
2										Previous	Fluff	New
3	Date USD	Close	Date GBP	Close		Name	Currency	Date	Amount	Amount, EUR
4	1/2/2012	$ 1.29	1/2/2012	£ 0.83		Alto	EUR	1/7/2012	1,712,773.00	0.00	1712773.00	1712773.00
5	1/3/2012	$ 1.32	1/3/2012	£ 0.83		Alto	EUR	5/23/2018	42,200.00	0.00	42200.00	0.00
6	1/4/2012	$ 1.29	1/4/2012	£ 0.83		Alto	EUR	12/14/2018	678,000.00	0.00	678000.00	0.00
7	1/5/2012	$ 1.28	1/5/2012	£ 0.83		Alto	USD	1/8/2012	1,395,000.00	0.00	1089843.75	1089843.75
8	1/6/2012	$ 1.27	1/6/2012	£ 0.82		Alto	EUR	3/25/2024	1,411,000.00	0.00	1411000.00	0.00
9	1/9/2012	$ 1.28	1/9/2012	£ 0.83		Alto	EUR	12/18/2020	102,000.00	0.00	102000.00	0.00
10	1/10/2012	$ 1.28	1/10/2012	£ 0.82		Alto	GBP	1/9/2012	1,154,000.00	1390361.45	1390361.45	1390361.45
11	1/11/2012	$ 1.27	1/11/2012	£ 0.83		Alto	GBP	3/24/2022	1,245,000.00	0.00	0.00	0.00
12	1/12/2012	$ 1.28	1/12/2012	£ 0.84		Alto	EUR	6/10/2022	510,000.00	0.00	510000.00	0.00
13	1/13/2012	$ 1.27	1/13/2012	£ 0.83		Alto	EUR	7/8/2022	26,000.00	0.00	26000.00	0.00
14	1/16/2012	$ 1.27	1/16/2012	£ 0.83		Alto	USD	10/21/2024	27,000.00	0.00	0.00	0.00
15	1/17/2012	$ 1.27	1/17/2012	£ 0.83		Alto	USD	10/30/2022	15,000.00	0.00	0.00	0.00
16	1/18/2012	$ 1.29	1/18/2012	£ 0.83
17	1/19/2012	$ 1.30	1/19/2012	£ 0.84
18	1/20/2012	$ 1.29	1/20/2012	£ 0.83
Sheet1 (2)

Cell Formulas
Range	Formula
M4:M15	M4	=IFERROR(IF(MATCH(K4,D:D,0)>0,IFS(K4="total",0,L4=0,0,J4="USD",IFERROR(IF(J4="USD",L4/(IFERROR(VLOOKUP(K4,D:E,2,FALSE),IFERROR(VLOOKUP(K4+1,D:E,2,FALSE),VLOOKUP(K4+2,D:E,2,FALSE)))),L4),""),J4="GBP",IFERROR(IF(J4="GBP",L4/(IFERROR(VLOOKUP(K4,F:G,2,FALSE),IFERROR(VLOOKUP(K4+1,F:G,2,FALSE),VLOOKUP(K4+2,F:G,2,FALSE)))),L4),""),J4="EUR",L4),0),0)
N4:N15	N4	=IF(OR(K4="total",L4=0),0,IF(J4="USD",IFNA(L4/VLOOKUP(WORKDAY(K4-1,1),$D$4:$E$5002,2,0),0),IF(J4="GBP",IFNA(L4/VLOOKUP(WORKDAY(K4-1,1),$F$4:$G$5002,2,0),0),L4)))
O4:O15	O4	=IFERROR(IF(MATCH(WORKDAY(K4,1),D:D,0)>0,IFS(K4="total",0,L4=0,0,J4="EUR",L4,J4="USD",IFNA(L4/VLOOKUP(WORKDAY(K4-1,1),D:E,2,0),0),J4="GBP",IFNA(L4/VLOOKUP(WORKDAY(K4-1,1),F:G,2,0),0)),0),0)

 

[Fluff]

Personally I would avoid using the IFS function on something like this as it is less efficient (and therefore slower) than nested IF functions.

 

[dorian25]

Well, guys, the formula works now thank you both for the help.

Much Appreciated!
Dorian

 

[Fluff]

Glad we could help & thanks for the feedback.

 

[ExceLoki]

happy to help