Currency conversation using a table in a separate sheet

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?

Find more posts tagged with

Formulas

Accepted answers

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

All comments

Carson Penticuff

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

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.

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.