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 06, 2025

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.

 

HansVogelaar's avatar
HansVogelaar
MVP
May 07, 2025

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?

  • 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.

    • dsmith52's avatar
      dsmith52
      Copper Contributor
      May 08, 2025

      Thank you.  That worked.  I should be able to work with that.  Thanks for the help.

       

    • 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