Subtraction across two sheets if row and column match

Options
Dan123
Dan123
edited 09/15/21 in Formulas and Functions

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?

Tags:

Answers

  • Jaykel Torres
    Options

    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
    Options

    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.
    Genevieve P. Employee Admin
    Options

    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?

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!