Subtraction across two sheets if row and column match
Hi community,
I have separate sheets for tracking monthly budget and actual expenditures. They have identical layouts (see below).
Problem is, the budget goes to 2024, so I really need a formula that can calculate the variance between budgeted and actual. The logic would be something like:
=budget(where row = "Feas591: Sample Preparation" AND column="1021")  actual expenditures(where row = "Feas591: Sample Preparation" AND column="1021").
The logic for the formula of the cell to the immediate right would be:
=budget(where row = "Feas591: Sample Preparation" AND column="1121")  actual expenditures(where row = "Feas591: Sample Preparation" AND column="1121").
Could this be done with two index(match()) statements, or maybe there's a better way?
Answers

Hey @Dan123,
You can use the INDEX and MATCH Functions together with Cross Sheet Referencing, to pull in specific Cell Data within a Sheet to another. I've created the example below on how this may look like:
Formula (results highlighted in yellow): =INDEX([2020 Price]:[2020 Price], MATCH("Apple", Fruit:Fruit, 0))  INDEX([Current Price]:[Current Price], MATCH(Fruit@row, Fruit:Fruit, 0))
 The first INDEX/MATCH statement is returning the 2020 Price of an "Apple"
 While the second INDEX/MATCH statement is returning the Current Price of an "Apple"
 We can then subtract the 2020 Price with the Current Price to calculate the difference
Note: I am not using Cross Sheet Referencing for the example's sake but you may find more info here: Crosssheet formulas.
I hope this helps!
Jaykel

Hi Jaykel,
Would this solution require me to manually code each column? I'm concerned because there are ~30 columns, plus I think I'd exceed the max number of references one sheet can make to another sheet.
What I'm really hoping for is a formula that references the entire range of date columns, but then specifies the right one  perhaps using the INDEX formula's ability to specify which column to examine?

Hi @Dan123
Yes, you can use the Column Index part of the Index function to find the correct column to pull from, however if you have 30+ columns to pull into this range you may reach crosssheet range limits with your two formulas.
Here's how the structure of one of these would work:
=INDEX({Full Range of Columns}, MATCH(TaskDescription@row, {Column with Task Description}, 0), MATCH([1021]$1, {Top Row with column names})
Then you can drag this to the right and the [1021]$1 should update to [1121]$1
Does that make sense? Will this work for you?
Help Article Resources
Categories
Check out the Formula Handbook template!