Log In
Register
Home
June 10, 2023
Offline
I'm making a mistake somewhere but I can't see it. Or it could be a rule I'm not aware of, or could be a bug.
I have multiple tables of data sorted by date (thousands of lines) that I want to sum to a single table. Each table starts on a different date. I can't seem to get it summed correctly for each date.
I have attached a simplified data set that exhibits the same issue I'm seeing in the full data set.
I would really appreciate suggestions for making this work. I know it would be better to use a pivot table but circumstances beyond my control prevent that.
Wayne
See Answer
Moderators
January 31, 2022
Offline
Made some changes to the file in order to make it work as you intended. Some comments, though.
1) The dates in the 2nd table were all from the year 1900. I assumed you intend to use 2024 dates.
2) The formula with structured table references applied the @ sign incorrectly and inconsistently. Some were correct, some were not. XLOOPUP expects a single look_up_value (thus use the @ sign), then a look_up_array (don't use the @ sign but just the column name), then a return_array (also without the @ sign). The last two arguments you used were correct.
3) The third formula with VLOOKUP also needed some adjustments. Firstly, you need to use the fourth argument FALSE to indicate that you are looking for an exact match. Secondly, VLOOKUP doesn't have the built-in feature to decide what to do if no match is found. It just returns #N/A! and then the end result will also be #N/A!. So you need to capture errors yourself with IFERROR and say that zero should be returned if not found.
Compare the formulas from the attached file with your original ones and see where they went wrong. Come back here if you have any further questions.
Answers Post
1 Guest(s)
