Trying to use index collect, vlookup and index to reference two columns on two sheets

Kelly Moore

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