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.
Best Answer
-
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")
Answers
-
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")
-
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.
-
I was able to plug in what I needed to your formula and it worked. Thank you @Hollie Green !
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 66.1K Get Help
- 430 Global Discussions
- 149 Industry Talk
- 490 Announcements
- 5.2K Ideas & Feature Requests
- 85 Brandfolder
- 154 Just for fun
- 74 Community Job Board
- 499 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 305 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!