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.

 

[etaf]

I'm Assuming there are ONLY 2 currencies entered
So the IF just looks for 1 value and returns the range to return

Book2
	A	B	C	D	E	F	G	H
1	Date	Amount	Currency	Result		DATE	RS	EU
2	3/2/20	1	RSD	1		1/4/20	1	10
3	8/8/20	1	EUR	20		8/4/20	2	20
4	1/5/21	1	RSD	3		1/1/21	3	30
5	2/15/21	1	EUR	40		2/1/21	4	40
Sheet1

Cell Formulas
Range	Formula
D2:D5	D2	=IF(C2="","",INDEX(IF(C2="RSD",$G$2:$G$5,$H$2:$H$5),MATCH(A2,$F$2:$F$5,1)))

 

[zdjbel]

Thank you very much for your offered solution Wayne. Your formula returns the rate of exchange which is fine in itself but solves half my problem. Using your formula I can add another column to perform the calculation and get the desired end result.
However, I'm looking for a solution where I wouldn't need to introduce an additional column into the worksheet. What I'd like it to do is for the formula to determine what is the expenditure currency in column C and the date of the expenditure in the same record/row, find the exchange rate for the corresponding date and currency in table B and then calculate the US Dollar equivalent. It's a tough ask I know, and I'd be very grateful if you could help me with this problem that's way out of my league.

Respectfully,
zdjbel

 

[etaf]

sorry about that, you did say that in the original question and forgot to add that step

index returns the conversion so should just be able to add the * to the formula

=IF(C2="","",INDEX(IF(C2="RSD",$G$2:$G$5,$H$2:$H$5),MATCH(A2,$F$2:$F$5,1)) * B2 )

 

[zdjbel]

No, that didn't work as I desired it to. Perhaps the image below will help explain things better. The correct USD equivalents for each record are in the Check column J, those are the numbers I ultimately want to be the results in column D. Looking at your formula, it may only be possible with multiple nested IF statements, one for each currency type, which may be too complicated and thus slow to run on a worksheet with 20,000+ records. If you agree with my assumption, I'll be happy to add another column and apply the first formula you suggested. I like the simplest solutions that work. Thank you in any case, I appreciate your time and the knowledge you shared here.

Respectfully,

zdjbel

 

[etaf]

58,181.82 RSD - on 10/1/2020
so the conversion is 100.38

58,181.82 / 100.38
is that correct

so your dividing by the conversion factor, not multiplying

so instead of * B2 then /B2
=IF(C2="","",B2/INDEX(IF(C2="RSD",$G$2:$G$5,$H$2:$H$5),MATCH(A2,$F$2:$F$5,1)) )

 

[zdjbel]

58,181.82 RSD - on 10/1/2020
so the conversion is 100.38

so your dividing by the conversion factor, not multiplying

so instead of * B2 then /B2
=IF(C2="","",INDEX(IF(C2="RSD",$G$2:$G$5,$H$2:$H$5),MATCH(A2,$F$2:$F$5,1)) / B2 )

Actually, if the currency is RSD then divide, if the currency is EUR then multiply, and if the currency is USD then multiply by 1

 

[etaf]

OK, that makes it a little more complicated, just some different Nested IF or an IF for * or /

you didnt mention USD

so you have three conversions in the Currency list

RSD = divide the amount in B2 by the rate of exchange
USD = same as the amount - NO conversion needed
EUR = Multiply the amount in B2 by the rate of exchange

is that the only options now ?
anything else to include ?

 

[etaf]

=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)),""))))

Assuming no additions
perhaps if you put the spreadsheet on a share like dropbox or onedrive
saves trying to remake the spreadsheet myself

 

[zdjbel]

=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)),""))))

Assuming no additions
perhaps if you put the spreadsheet on a share like dropbox or onedrive
saves trying to remake the spreadsheet myself