How to calculate difference of two sheets into a separate grid

Options

I have data in four grids; a master (G1) and three data sets (G2, G3, G4).

I need to return the data results of G2 minus G3 as a line item in G1 but will require a "vlookup" to do so. Separately, I need to know if there is a difference "Yes/No" between G4 and G3 on the same line but in a different column.

I've included a screenshot that represents each grid. Each grid has the same headers but not in the same order.


Answers

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Options

    You would use two INDEX/MATCHes and subtract one from the other.


    =INDEX({Sheet 2 # Column}, MATCH([ID Number]@row, {Sheet 2 ID Column}, 0)) - INDEX({Sheet 3 # Column}, MATCH([ID Number]@row, {Sheet 3 ID Column}, 0))


    For the Yes/No comparison, you would use a similar set of INDEX/MATCHes and pull them into an IF statement that basically says if they don't match, do this, otherwise that.


    =IF({Sheet 2 Yes/No Column}, MATCH([ID Number]@row, {Sheet 2 ID Column}, 0)) <> INDEX({Sheet 3 Yes/No Column}, MATCH([ID Number]@row, {Sheet 3 ID Column}, 0)), "what to output if they do not match", "what to output if they do match")

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!