Forum Discussion

dsmith52's avatar
dsmith52
Copper Contributor
May 05, 2025
Solved

Hot to create a summary table by pulling data from two other tables using common ranges

Good day:  First, please note that I am blind, so need clear text based instructions.  I have a workbook containing three sheets.  One sheet has data in three columns, with columns a,b and c containi...
admin
excel
Formulas and Functions
  • HansVogelaar's avatar
    HansVogelaar
    May 08, 2025

    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.

     

dsmith52's avatar
dsmith52
Copper Contributor
May 07, 2025

I have attached a sample file.  Excel reports a circular reference error on Sheet3 in cell a1.  Thanks.

HansVogelaar's avatar
HansVogelaar
MVP
May 08, 2025

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.

 

Resources