Hi,

I'm looking for a formula to bring information from a different tab with multiple criteria to the main workbook. I'm thinking about index & match but was unable to make it work.



Please help! I attached the workbook herewith.

Many thanks in advance.

Attached Files

Book3.xlsx (103.0 KB, 5 views)

Does the attached help?


BTW are you still using XL 2010? ( recent versions have other useful functions)

Attached Files

Book3.xlsx (142.8 KB, 8 views)

__________________
Did you know you can thank someone who helped you? Click on the tiny scale in the right upper hand corner of your helper's post

Oh this is brilliant!
Many thanks for looking into this so quickly for me.

Best,

Please mark thread as solved ( under "Thread Tools"). Thx

__________________
Did you know you can thank someone who helped you? Click on the tiny scale in the right upper hand corner of your helper's post

Another way.

I removed GBP format from Debit, Credit, and Total columns, as it did feel wrong to show e.g. AUD values formatted as GBP. And I added a calculated currency column to Data sheet - it was possible to write the formula for conversion rate so it read the currency directly from Branches sheet, but in this way the formula is easier (and it will be useful to see the local currency in Data table too).

With this design, you haven't change the design of Data and Rates worksheets, when any new branches/currencies are added in future. And you haven't to redesign your Rates sheet and rate calculation formula on Data sheet every month too!

Another thing to consider - change the format for Month columns from "mmm yyyy" to "yyyy mmm" (e.g. "2022 Apr"). Then it will be possible to sort your tables by month, when you need this (and months data validation list will work better too). As you use text values for months, this means you have to use a temporary columns in all tables with Month column to convert current month names into new format, and then overwrite the old values with new ones.

Attached Files

CurrencyConversionExample.xlsx (154.4 KB, 3 views)