Currency conversation using a table in a separate sheet

xahndra

I would like to use collect/look-up to reference and convert amounts into USD.

Columns on the sheet where the conversation needs to take place:

• Reimbursement Amount
• Currency 3-letter code

If the 3 letter code is USD, copy the amount, if it is not USD, it needs to look up 3-letter code and times it by the conversation rate.

• External sheet reference: {Currency} - this is the 3 letter code
• External sheet reference: {Rate} - our company conversation rate

Could someone help me figure out the best formula to use?

Carson Penticuff

I will give you two options. This should work fine, but if a 3 digit code that does not appear in your conversion sheet is entered by mistake, it will return "#NO MATCH". I personally prefer this is most situations, as it draws attention to the issue.

=IF(AND([Reimbursement Amount]@row <> "", [Currency 3-letter code]@row <> ""), IF([Currency 3-letter code]@row = "USD", [Reimbursement Amount]@row, [Reimbursement Amount]@row * INDEX({Rate}, MATCH([Currency 3-letter code]@row, {Currency}, 0))), "")

If you would instead prefer to show a blank cell in this situation, you can use this:

=IFERROR(IF(AND([Reimbursement Amount]@row <> "", [Currency 3-letter code]@row <> ""), IF([Currency 3-letter code]@row = "USD", [Reimbursement Amount]@row, [Reimbursement Amount]@row * INDEX({Rate}, MATCH([Currency 3-letter code]@row, {Currency}, 0))), ""), "")

Carson Penticuff

I will give you two options. This should work fine, but if a 3 digit code that does not appear in your conversion sheet is entered by mistake, it will return "#NO MATCH". I personally prefer this is most situations, as it draws attention to the issue.

=IF(AND([Reimbursement Amount]@row <> "", [Currency 3-letter code]@row <> ""), IF([Currency 3-letter code]@row = "USD", [Reimbursement Amount]@row, [Reimbursement Amount]@row * INDEX({Rate}, MATCH([Currency 3-letter code]@row, {Currency}, 0))), "")

If you would instead prefer to show a blank cell in this situation, you can use this:

=IFERROR(IF(AND([Reimbursement Amount]@row <> "", [Currency 3-letter code]@row <> ""), IF([Currency 3-letter code]@row = "USD", [Reimbursement Amount]@row, [Reimbursement Amount]@row * INDEX({Rate}, MATCH([Currency 3-letter code]@row, {Currency}, 0))), ""), "")

xahndra

Thank you so much! I am getting an invalid reference even though I see the 3-letter code is on both sheets. Any idea why?

Carson Penticuff

I would go into the reference manager and verify that both of the references are correct. Maybe a typo, extra space, something of that nature.