Subtraction across two sheets if row and column match

Dan123

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 = "Feas-591: Sample Preparation" AND column="10-21") - actual expenditures(where row = "Feas-591: Sample Preparation" AND column="10-21").

The logic for the formula of the cell to the immediate right would be:

=budget(where row = "Feas-591: Sample Preparation" AND column="11-21") - actual expenditures(where row = "Feas-591: Sample Preparation" AND column="11-21").

Could this be done with two index(match()) statements, or maybe there's a better way?

Jaykel Torres

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: Cross-sheet formulas.

I hope this helps!

Jaykel

Dan123

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?

Genevieve P.

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 cross-sheet 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([10-21]$1, {Top Row with column names})

Then you can drag this to the right and the [10-21]$1 should update to [11-21]$1

Does that make sense? Will this work for you?