Multiple lookups

[kashaikh78]

Objective: I want to use multiple lookup function to calculate the profit based on currency.

In sheet1, I have two sets of data. first set is from column A to D and second set of data is from Column F to AI.


I want to search the file reference no in column G to the first data set in column A and lookup the corresponding currency in Column D. If the reference number matched and the Currency is CAD then return the amount of column C in column AF. If the currency is USD then convert the amount in Column C to CAD and return the value in column AH. Also if reference number is not matched then return blank in column AF & AH.

I am using the Index & match function but for some reason it is not returning the correct result.

{=INDEX(A3:D5364,MATCH(1,(A:A=G3)*(D:D="CAD"),0),3)}