Debt Planner / Category Match Formula Question

Google Sheets
, , ,
1

:wave: I’ve been using the debt planner solution for quite some time, and love it. One of the things that I’ve done is set up another sheet to transpose the balance and payment modelers.

I then use these transposed numbers in another sheet to track my spending and debt pay off a few years in advance.

For example, I have a sheet called Transposed Balance which contains one formula:

=SORT(TRANSPOSE('Debt Planner'!X3:AW52))

I then reference the values of this on another sheet with a list of all of my debts in Column A, and then another column for EOY estimated balance, where $O, in this case, is December for my 2022 EOY Balance


=IF(ISBLANK(IFNA(INDEX('Transposed Balance'!$O$2:$O$30,MATCH($A2,'Transposed Balance'!$A$2:$A$30,0)),0)),0,IFNA(INDEX('Transposed Balance'!$O$2:$O$30,MATCH($A2,'Transposed Balance'!$A$2:$A$30,0)),0))

This works awesomely, but I’m struggling with another aspect of it, and that’s taking my 2nd transposed sheet for the payment modelers, and auto-filling it into the categories with the appropriate values. I use the following formula to grab the appropriate section for the accounts, the month, and the suggested amount to pay.

=SORT(TRANSPOSE('Debt Planner'!AY3:BZ52))

What I have been doing historically is copying this sheet’s values into another sheet, and then just copying and pasting the appropriate budget in the category based on this.

Below is an example of what the transposed data looks like:

Account Min Payment 1/1/2022 2/1/2022
Apple Card $200.00 $200.00 $200.00
SQ5 $841.64 $841.64 $841.64
Student Loan 1 $20.00 $20.00 $20.00
Student Loan 2 $20.00 $20.00 $20.00
TTRS $1,229.13 $1,229.13 $1,229.13

The goal is for the Categories Sheet to reflect what’s on this sheet.

I’ve tried a few different MATCH+INDEX combos, and I’m not able to wrap my mind around it.

2

Looks like you’re doing awesome stuff, @branadonshutter. I wish I had the advanced skills to help you here, but I don’t. Perhaps @jono or @randy has some insights?

3

I ended up figuring it out but forgot to post the solution here. I’m gonna figure out a way to sanitize this data and probably offer it as a community solution, as I’m sure others could use it.

In lieu of that, I’ve provided a sample sheet with the solution.

The Source Data sheet would the transposed data from the Debt Planner sheet.
The Final Place is the Categories sheet.
The Expected Outcome was just that.

So first, what I couldn’t figure out was the offset for match/index, and it didn’t click that I could do another match to find the offset.

=index(
    'Source Data'!$A$2:$D$26,
    match(
        $A3,
        'Source Data'!$A$2:$A$26,
        0
    ),
    match(
        D$2,
        'Source Data'!$A$1:$D$1,
        0
    )
)"

Once I figured that out it was just a matter of some ifna checking and replacing with 0 if not found.

=if(
    isna(
        match(
            $A15,
            'Source Data'!$A$2:$A$26,
            0
        )
    ),
    0,
    if(
        index(
            'Source Data'!$A$2:$BB$26,
            match(
                $A15,
                'Source Data'!$A$2:$A$26,
                0
            ),
            match(
                N$1,
                'Source Data'!$A$1:$BB$1,
                0
            )
        ) = 0,
        0,
        index(
            'Source Data'!$A$2:$BB$26,
            match(
                $A15,
                'Source Data'!$A$2:$A$26,
                0
            ),
            match(
                N$1,
                'Source Data'!$A$1:$BB$1,
                0
            )
        )
    )
)
4

Looks like you’ve got it, @branadonshutter. Your lookup formula looks similar to the one @Brad.warren used to map his Category Schedule into his Categories budgets.

5

Oh, that might have been a nice place to look for inspiration haha. I’ll look there now and see if my stuff can be cleaned up any.