Frequent Visitor

# I need help to find the correct formula, accounting

Hi

I have put my accounting data in to Excel. Different expenses should be placed in different columns. E.g tools, food, fee, etc. Lets say I have 5 different expense categories, but now all expense values are in column C. I need a formula for typing the value 1-5 (1=tools, 2=food etc) in a cell in column D, that copies the value in C to E, F, G, H and I, according to the category of the expense. What formula can I use to do this?

3 Replies

# Re: I need help to find the correct formula, accounting

Let's say the data begin in row 2.

In E2: =IF(D2=1,C2,"")

In F2: =IF(D2=2,C2,"")

In G2: =IF(D2=3,C2,"")

In H2: =IF(D2=4,C2,"")

In I2: =IF(D2=5,C2,"")

Select E2:I2, then fill or copy down.

# Re: I need help to find the correct formula, accounting

I use 365 and use defined names.

``= IF(categoryExpense=categoryHdr, expense, "")``

# Re: I need help to find the correct formula, accounting

I have another method you can try using a combination of INDEX, MATCH, and ROW functions! You can use the following formulas for your 5 categories: In column E: =IFERROR(INDEX(\$C\$2:\$C\$100, MATCH(1, (\$D\$2:\$D\$100=1)(ROW(\$D\$2:\$D\$100)-ROW(\$D\$2)+1), 0)), "") In column F: =IFERROR(INDEX(\$C\$2:\$C\$100, MATCH(2, (\$D\$2:\$D\$100=2)(ROW(\$D\$2:\$D\$100)-ROW(\$D\$2)+1), 0)), "") In column G: =IFERROR(INDEX(\$C\$2:\$C\$100, MATCH(3, (\$D\$2:\$D\$100=3)(ROW(\$D\$2:\$D\$100)-ROW(\$D\$2)+1), 0)), "") In column H: =IFERROR(INDEX(\$C\$2:\$C\$100, MATCH(4, (\$D\$2:\$D\$100=4)(ROW(\$D\$2:\$D\$100)-ROW(\$D\$2)+1), 0)), "") In column I: =IFERROR(INDEX(\$C\$2:\$C\$100, MATCH(5, (\$D\$2:\$D\$100=5)*(ROW(\$D\$2:\$D\$100)-ROW(\$D\$2)+1), 0)), "") Please make sure to enter these as array formulas by pressing Ctrl+Shift+Enter when entering them in the respective cells. Copy the formulas down the columns, and they should distribute the values based on the category in column D. Funny story, while I was wrestling with my own accounting spreadsheets, I discovered sfb.group and got in touch with them. They connected me with a fantastic accountant who ironed out all my accounting kinks. It was such a lifesaver!