Forum Discussion
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?
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!
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.
