Adding a Category Type Column to the Transactions Sheet

Google Sheets Show & Tell
1

I realize this is an older thread, but it’s what I find when searching the forum on how to add the category type to the Transactions sheet. I couldn’t find if it’s still an issue to use a Type labelled column in the transactions sheet.

Anyhow, for anyone else searching/finding this thread who is starting from a new Foundation Template, here is what seems to work for me:

={"Category Type";ARRAYFORMULA(IFERROR(XLOOKUP($D2:$D,Categories!$A$2:$A,Categories!$C$2:$C)))}

These are the column references for a new sheet today, which is different than the other examples in this thread.

1 Like
2

Hey @Mark.S! This post was a little lost at the bottom of the thread where you originally posted it so I moved it here as a standalone thing.

I hope you don’t mind but I made a few small tweaks to the formula to simplify it a bit.

As you noted, anyone deploying it in their spreadsheet should make adjustments if:

  • The Transactions/Category column is not D:D
  • The Categories/Category column is not A2:A
  • The Categories/Type column is not C2:C

Thanks so much for sharing your learnings.

1 Like
3

Very nice @randy, thank you, and I’ve incorporated your small tweaks :wink:

4

Do I dare bring up the similar idea of the Group Column formula in help center article Add a Group column to the Transactions Sheet
?

Check out this beauty. For consistency, it seems both would be done the same way, though this one is a bit more intimating :thinking:
=arrayformula(if(row($D$1:$D)=1,"Group",iferror(vlookup($D$1:$D,{indirect("Categories!$"&SUBSTITUTE(ADDRESS(1,MATCH("Category",Categories!$1:$1,0),4),1,"")&"$2:$"&SUBSTITUTE(ADDRESS(1,MATCH("Category",Categories!$1:$1,0),4),1,"")),indirect("Categories!$"&SUBSTITUTE(ADDRESS(1,MATCH("Group",Categories!$1:$1,0),4),1,"")&"$2:$"&SUBSTITUTE(ADDRESS(1,MATCH("Group",Categories!$1:$1,0),4),1,""))},2,FALSE),"")))

5

Your original works great but I think the static array approach is a little simpler for the header and XLOOKUP is a little easier to read than VLOOKUP and it better handles moving columns.

6

To add the corresponding Category Group column to the Transactions Sheet:

={"Category Group";ARRAYFORMULA(IFERROR(XLOOKUP($D$2:$D,Categories!$A$2:$A,Categories!$B$2:$B)))}

Where Categories/Group column is B2:B

Note that a potential gotcha with using formulas like this in the Transactions Sheet occurs with copy-pasting Values-only workflows that modify these columns - then these columns will be blank besides the entries with pasted values. Normal copy-paste of rows within the Transaction Sheet will work though.

7

I’m finding that the XLOOKUP $D2 gets incremented to $D3 after using Add Manual Transaction. It continues to increment with every Add Manual Transaction. $D$2 doesn’t fix it. $D:$D also doesn’t. Then all the Category Types are incorrect.

That doesn’t happen and all is good with the original formula:
=ARRAYFORMULA(IF($D:$D = "Category", "Category Type", IFERROR(VLOOKUP($D:$D,Categories!$A$2:$C,3,FALSE),"")))

Your thoughts?

8

@randy I’m finding that when using your tweaked version of the formula, adding INDIRECT to put the cell reference inside a string, prevents the formula’s cell reference incrementing after using Add Manual Transaction.

Like so:

={"Category Type";ARRAYFORMULA(IFERROR(XLOOKUP(INDIRECT("$D2:$D"),INDIRECT("Categories!$A$2:$A"),INDIRECT("Categories!$C$2:$C"))))}

={"Category Group";ARRAYFORMULA(IFERROR(XLOOKUP(INDIRECT("$D2:$D"),INDIRECT("Categories!$A$2:$A"),INDIRECT("Categories!$B$2:$B"))))}

9

I just tried this code for adding the group and it seemed to work fine, even after doing a manual transaction:
={"Group";IFERROR(BYROW($D$3:$D,LAMBDA(category,XLOOKUP(category,Categories!$A$2:$A,Categories!$B2:$B))),"")}

10

Notice how BYROW is referencing $D$3? I’ll bet that was $D$2 before the manual transaction.

And I’ll bet your group column is shifted by one now, with incorrect entries.

11

Ahh, I get what you’re saying now, and you’re right, I didn’t notice the groups and categories weren’t aligned properly anymore. I switched the formula to use an INDIRECT as you suggested and it does then work as expected:
={"Group";IFERROR(BYROW(INDIRECT("$D2:$D"),LAMBDA(category,XLOOKUP(category,Categories!$A$2:$A,Categories!$B2:$B))),"")}

12

Yep! And for fun, insert a row at the top of your Category sheet to see your Category sheet variables in your formula change in the same way.

I understand inserting rows in the Category sheet causes data validation issues, likely for similar reasons, and shouldn’t be done … but, it just shows the interaction. It’s a feature :wink: