Check for Matching Values on Another Sheet

OK, so I have two columns on multiple sheets, ESTIMATED PROJECT SIZE and ACTUAL PROJECT SIZE. On the sheet I'm working in, I have a column called PROJECT SIZE DIFFERENCE.

Essentially, I want to write a formula in my current sheet that checks both columns on one of the other sheets (one at a time, so I'll have to write multiple formulas, I'm sure), and if those values match, return a No. If the values do not match, return a Yes.

I'm at a loss as to how to do this, but in my head it seems like it should be simple…

Thank you in advance.

Find more posts tagged with

Formulas

Accepted answers

Hollie Green

If you add an auto row or row number column that matches up to an auto row or row number column on your current sheet can probably get away with one formula.

=IF(INDEX(COLLECT({Column 1 on reference sheet}, {Auto row on Reference Sheet}, [Matching column for Row # on destination sheet]@row), 1) = INDEX(COLLECT({Column 2 on reference sheet}, {Auto row on Reference sheet}, [Matching column for Row # on destination sheet]@row), 1), "Yes", "No")

All comments

Hollie Green

If you add an auto row or row number column that matches up to an auto row or row number column on your current sheet can probably get away with one formula.

Jerexify

Is there any way to do it by matching data from another column? For instance, in the Index formulas on the sheet, I use a Match insert to match the PROJID on one sheet to the PROJID on the reference sheet. Is that possible?

We're trying to go avoid adding more columns.

Jerexify

I was able to plug in what I needed to your formula and it worked. Thank you @Hollie Green !

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, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.