Forum Discussion
Hot to create a summary table by pulling data from two other tables using common ranges
Thanks! My formula contained an error left over from experimenting, but it was also incorrect - it looked up only the product on Sheet 2, instead the combination of product and payment method.
The formula should be
=LET(Condition, XLOOKUP(Sheet2!A1:A100, Sheet1!A1:A100, Sheet1!C1:C100, "")=E1, Products, FILTER(Sheet2!A1:A100, Condition, ""), Payment, FILTER(Sheet2!B1:B100, Condition, ""), Amount, XLOOKUP(Products, Sheet1!A1:A100, Sheet1!B1:B100, 0)*XLOOKUP(Products&Payment, Sheet2!A1:A100&Sheet2!B1:B100, Sheet2!C1:C100, 0), HSTACK(Products, Payment, Amount))
I have attached the corrected workbook.
I hope that this works in Excel 2024.
Enter the vendor that you want to summarize in E1 on Sheet 3. In your example that would be company a.
Enter the following formula in A1 on Sheet 3, replacing Sheet 1 and Sheet 2 with the names of those sheets in your workbook.
=LET(Condition, XLOOKUP('Sheet 2'!A1:A100, 'Sheet 1'!A1:A100, 'Sheet 1'!C1:C100, "")=E1, Products, FILTER('Sheet 2'!A1:A100, Condition, ""), Payment, FILTER('Sheet 2'!B1:B100, Condition, ""), Amount, XLOOKUP(Products, 'Sheet 1'!A1:A100, 'Sheet 1'!B1:B100, 0)*XLOOKUP(A1:A2, 'Sheet 2'!A1:A100, 'Sheet 2'!C1:C100, 0), HSTACK(Products, Payment, Amount))
Thanks for the reply, however when entering the formula as written, Excel complains that there are a number of circular references that can't be resolved, and puts an error in the cell where the formula is. I have not fully figured out the formula (I will have to look up some of the commands/functions), but I also did not find a reference to the E1 cell mentioned in the instructions in the listed function. Any suggestions? Thanks.
Could you attach a small sample workbook demonstrating the problem (without sensitive data), or if that is not possible, make it available through OneDrive, Google Drive, Dropbox or similar?
I have attached a sample file. Excel reports a circular reference error on Sheet3 in cell a1. Thanks.
