Hello,

If you attach a sample file with some data it would be much easier to give you a correct answer. The XLOOKUP function can return values in many ways, as explained in the blog article linked to above. But perhaps your scenario is not covered by the many examples?

Depending on what Excel version you use, there are probably other functions that could be better to use.

Br,
Anders

If you also include the Excel version you're using and a sample file as Anders stated ...

hi

I have attached a file as an example

1. this sheet shows product codes, superseded from & superseded to codes
2. this sheet shows All the transactions over a rolling 12 months period
3. this is an example of product ALTS004 and superseded from and to codes 

I am looking for a formula that checks the codes from sheet 1 per row against sheet 2 and then be able to total product, superseded to and superseded from per rolling 12 month and grand total as per example sheet 3

Regards

RG

I currently use office 365 for excel

I did some playing around with formulas, I do not have 365 but am using version 2021 so many if the new functions are unavaliable to me.

The formulas work fine and return one series but for some reason it only workds once and the rest return an error.

The errors are not shown because I use IFERROR statement but maybe you can use it 

The Sheet 1 is populated and filtered 

I could make this work using VBA but this should work too.

Hope you understand my approach with the formulae

I would prefer a solution with Power Query where you can do a few merge/expand steps to 'join' the values for the codes with the codes "from" and "to" by month and load the end result into a pivot table.

For some reason I can't attach a file to my response, so please try to download the file from my OneDrive in the link below.

RG_on-lining-training-example_v2.xlsx

Hello,

I would go with Power Query too, but a formula version I would use is

=SUM(('Sheet 2'!$E$4:$P$2558)*(('Sheet 2'!$A$4:$A$2558=$A3)+('Sheet 2'!$A$4:$A$2558=$B3)+('Sheet 2'!$A$4:$A$2558=$C3))*('Sheet 2'!$E$2:$P$2=E$1)*('Sheet 2'!$E$3:$P$3=E$2))

See attached file.

Br,
Anders

@Anders: I took the liberty to use the formulas from your file to sum the periods from Nov to Oct in the previous file I had attached.

This way it's dynamic as goes for the 4 first columns and the summing is consistent with your solution.

Makes it more dynamic for the OP to add data to sheet2, Column C in sheet1 is filtered to ignore cells containing the work (blank) and blank cells

many thanks to Anders & Hans just what i needed.

I'm a quick learner so can now use or adapt the formula for forthcoming work........ look forward to it now I understand  

Thanks to this site and ... everyone here is great, I like to read the posts, but-in once in a while, and learn something every time.

Just in case a PQ solution is still considered an option, and to avoid that the file link above expires, I believe I can now upload the file.