Possible Indirect Function Needed or Any feasible method

Hello Everyone,

I have a Indirect Function questions or maybe something else that could possibly be quicker. I am not sure my method or thought process is the best?

Template Sheet is used has a guide to pull data from thousands of accounts based on the reference Invoice No (client ID + Invoice No "Column A and F") and SK AK NO ( Account "Column C")

The reference sheets are already designated by name Invoice No. My objective is to pull the Invoice Charge (Column S) from each designated account. But there is one catch, based upon the currency I must either place in column I or J.


Column I - is for any currency other than USD

Column J - is for USD

I believe that the Indirect Function is the best practice for this method but I may be wrong. I do not know if there is a Macro or even just a better practice.


What I have come up with is the following but I can't seem to apply the currency options to place in one of the following columns.


=Index(Indirect("'46547-20241100041'!C:C,Match("666457",Indirect("46547-20241100041'!S:S"),False),1)

I have attached a Completed Template with the data provide to demonstrate what I am wanting to accomplish. I would really appreciate the assistance with this task.

OK cvici,

Not an easy formula but I got it. Looks like:

Formula:

Please Login or Register  to view this content.

Find the formula in your well-constructed example attached. Indirect Match Other Sheets.xlsx
The Not Equal changes to Equal in second column.

Hi cvici,

When I was playing with your data you had spaces in front and behind lots of your data. My first guess is that you need to Trim() your data before you do the formulas. Also ensure the data types are the same when doing a Match along with removing spaces.

..............

..............