Reference range when column matches string

0
votes

Budget spreadsheet. Column A contains categories, Row 1 contains paycheck dates, and each cell from B2:AE91 contains numeric values ("how much I spent on categoryX during paycheckY").

Named ranges:

  • Column A - "Budget_LineItem"
  • Row 1 - "Budget_PayPeriods"

On another tab, I have a list of specific categories called "Funds," where I want to track how much I've saved so far each paycheck toward the category by adding up the category's values each paycheck up until TODAY().

For example: | | A | B | C | D | | - | - | - | - | - | | 1 | Fund | Balance | Today: | =TODAY() | | 2 | Auto Insurance | =SUMIF(Budget_PayPeriods,"<="&MAX($D$1:$D$2),Budget!F48:AE48) | Projected Date: | |

As you can see, I just have a static range for the "Auto Insurance" category: Budget!B48:AE48. This works, but I want a formula that looks up the adjacent value in column A against the Budget_LineItem range, and returns the row range from B:AE in the Budget spreadsheet.

Basically reads: "Go find how much I've saved/spent so far toward categoryX in the Budget tab, and add up all the values for each paycheck up through today."

I know I'm close, but I can't make INDEX, MATCH, or any of the LOOKUP functions do what I need. I just can't figure it out.

EDIT: Here's a link to an example: https://docs.google.com/spreadsheets/d/1L4mlMrRCWwDNPSiYHpmFiXU1zNOnga6gAziz_m2awKI/edit?usp=sharing I also made a change to the OP formula in B2 as I realized it didn't work. I had tweaked it because my original formula had extra complexity and I was trying to KISS for this question. I changed it back to the more complex version so it works properly now.

1
google-sheetsgoogle-sheets-formulaspreadsheet
share a copy of your sheet - player0
done, see OP edit - Willman
sheet is private - player0
sorry @player0, should be fixed now - Willman

1 Answers

1
votes

delete range B2:B and use this in B2:

=INDEX(MMULT(FILTER(Budget!B2:4, Budget!B1:1<=MAX(D1:D2))*1, 
 SEQUENCE(SUMPRODUCT((Budget!B1:1<=MAX(D1:D2))))^0))

enter image description here

update:

=INDEX(IFNA(VLOOKUP(A2:A, 
 {Budget!A2:A4, MMULT(FILTER(Budget!B2:4, Budget!B1:1<=MAX(D1:D2))*1, 
 SEQUENCE(SUMPRODUCT((Budget!B1:1<=MAX(D1:D2))))^0)}, 2, 0)))

enter image description here