Active Member
October 18, 2024
I can do a XLOOKUP to find one cell against a sperate excel sheet to display relevant Info but can you find multiple cells at same time from separate sheet.
i.e if a wanted to find example1 & example2 in separate excel sheet and display relevant Info..... in this example Qty
example 1 product = 20
example 2 superseded product = 35
I am trying to find multiply values (product code that as supersede product) in one sheet that appear in other sheet and then add them together to get total quantity of old and supersede product combined
VIP
Trusted Members
December 7, 2016
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
Trusted Members
October 17, 2018
Active Member
October 18, 2024
hi
I have attached a file as an example
- this sheet shows product codes, superseded from & superseded to codes
- this sheet shows All the transactions over a rolling 12 months period
- 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
Trusted Members
October 17, 2018
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
Moderators
January 31, 2022
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.
VIP
Trusted Members
December 7, 2016
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
Trusted Members
October 17, 2018
@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
1 Guest(s)