Calculate field by matching 2 criteria in main table with 2 criteria in another table

[zdjbel]

How can I calculate the USD Equivalent of the Amount in Table A with the rate of exchange in Table B which matches the date and currency of the record in Table A. It must be done using a formula, not VBA or macros.

Thank you.

 

[zdjbel]

Try this link to see the spreadsheet. First time sharing something on Dropbox, hope I got it right.

Dropbox - Error - Simplify your life

www.dropbox.com

 

[zdjbel]

Yes, it works like a charm now, thank you sooo much, you saved me hours if not days trying to figure it out if at all.

Respectfully,

zdjbel

 

[etaf]

ok, so all good then - you are welcome -
FYI - the linked book worked OK - however, we prefer you to use

XL2BB as that stays in the forum , if you remove the dropbox file, then that is lost from the forum, and someone searching would not get the info to help them

 

[zdjbel]

Understood, thank you for the instructions.

Table A Table B.xlsx
	A	B	C	D	E	F	G	H	I	J
1	Date	Amount	Currency	USD Equivalent		Date	RSD	EUR		Check
2	10/4/2021	58,181.82	RSD	$ 582.11		10/1/2020	100.38	0.8540		$ 582.11
3	10/4/2020	17,100.00	EUR	$ 20,065.71		10/2/2020	99.90	0.8500		$ 20,065.71
4	10/4/2020	27,395.60	RSD	$ 274.09		10/3/2020	99.90	0.8500		$ 274.23
5	10/7/2020	$5,000.00	USD	$ 5,000.00		10/4/2020	99.95	0.8522		$ 5,000.00
6	10/9/2020	141,519.48	EUR	$ 166,063.69		10/5/2020	99.90	0.8524		$ 166,024.73
7						10/6/2020	99.85	0.8509
8						10/7/2020	99.57	0.8480
9						10/8/2020	99.52	0.8487
10						10/9/2020	99.97	0.8500
Sheet 1

Cell Formulas
Range	Formula
D2:D6	D2	=IF(C2="","",IF(C2="USD",B2,IF(C2="RSD",B2/INDEX($G$2:$G$5,MATCH(A2,$F$2:$F$5,1)),IF(C2="EUR",B2/INDEX($H$2:$H$5,MATCH(A2,$F$2:$F$5,1)),""))))
J2	J2	=B2/G5
J3	J3	=B3/H5
J4	J4	=B4/G4
J5	J5	=D5*1
J6	J6	=B6/H6

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
F2:H10	Expression	=MOD(ROW(),2)=0	text	NO

 

[etaf]

you are welcome
looks like its working now OK

 

[zdjbel]

Yes, thank you again for the immense help with this.