0

I need help on a formula.

I have set up a worksheet with 24 tabs. 12 review sheets, one for each month. 12 sheets for the P&L each month.

I want to copy and paste the p and l into the relevant month and have the data extract into the review sheet.

On the p and l, there are various cost centres. In the cost centres there are various spend categories. Some of the spend categories repeat so I need a formula that ensures it pulls the data from the correct cost centre AND spend category. I used a v look up with an if error but it hits a wall when the spend categories repeat. In the image, you will see that Uniforms and Footwear as an example appears in two cost centres, so V Look up only finds the first one. I need it to find the value that matches both the spend category and the cost centre.

Please help

Thank you

enter image description here

7
  • 1
    What have you tried so far and what is the exact problem you are facing? meta.stackoverflow.com/questions/284236/… May 21, 2022 at 18:05
  • I have tried =IFERROR(VLOOKUP(B141,'JAN P&L'!$A$19:$B$360,2,FALSE),"")
    – Chris
    May 21, 2022 at 19:15
  • this basically searches works except when a spend category is present in two different cost centres, it picks up the first answer again
    – Chris
    May 21, 2022 at 19:17
  • When you say p and l is that an I or an L? As in columns P and I or the sheet P&L? May 23, 2022 at 8:22
  • 1
    I would separate your cost center and spend category into 2 different columns in all sheets. Then you can use a SUMIFS formula.
    – Isolated
    May 23, 2022 at 19:25

0

You must log in to answer this question.

Browse other questions tagged .