Trying to use index collect, vlookup and index to reference two columns on two sheets
Answers
-
Hey @xahndra
Yes, I can help with this. If I understand you correctly, you have your currency conversion rate and currency code on a separate reference sheet sheet
Your formula will look something like this- remember you will have to build the cross sheet references yourself - you cannot directly copy this formula into the sheet unless these references already exist in your sheet. You need to edit this formula to insert the name of your currency column on the active sheet.
=[Reimbursement Amount]@row * VALUE(INDEX({Rate},MATCH([whatever the name of your Curr Code column on active sheet]@row,{Currency},0)))
So whether you multiply by the rate (as I have shown above) or divide by the rate depends on how your company has their conversion rates listed. Do they list it by other currency per USD or USD per other currency. I automatically assumed it would be USD per other currency but then realized I shouldn't make that assumption.
So see if this calculation works for you. If it doesn't, try dividing instead. Let me know if neither works.
Kelly
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.9K Get Help
- 441 Global Discussions
- 139 Industry Talk
- 471 Announcements
- 4.9K Ideas & Feature Requests
- 129 Brandfolder
- 148 Just for fun
- 68 Community Job Board
- 496 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 300 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!