How to calculate difference of two sheets into a separate grid

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.

Find more posts tagged with

Accepted answers

All comments

Paul Newcome

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")

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, latest, sort, sortType, lookupSort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.