Checking a box on one sheet if criteria are met in two columns on another sheet
I'm trying to check a box on my master sheet if the ID number on my master sheet matches the ID number on another sheet and if the status for that ID number is "Resolved." I've tried several approaches but can't seem to get a formula that works. Any help you can provide is greatly appreciated!
Answers
-
It sounds like you are looking to use an INDEX MATCH combo. If I'm reading it right, both the ID# and the Status are on another sheet, and on your sheet you have the ID# to match, and a checkbox. If that's correct, you can use a formula like this:
=IF(INDEX([Status (other sheet)]:[Status (other sheet)], MATCH([ID Number]@row, [ID Number (other sheet)]:[ID Number (other sheet)], 0)) = "Resolved", 1, 0)
Jason Tarpinian - Sevan Technology
Smartsheet Aligned Partner
-
I think we're getting closer - thank you! The sheet I'm pulling data from (the one with the two columns) has one column that is a checkbox (I'm targeting rows that are unchecked), and the other column is the one with the ID I'm hoping to match. I can't get the formula you provided above to work, and I can't seem to figure it out from here. Can you spot check the following?
=IF(INDEX([{MENP Range 1}]:[{MENP Range 1}], MATCH([Claimant ID]@row, [{MENP Range 2}]:[{MENP Range 2}], 0) = 0, 1, 0))
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.9K Get Help
- 379 Global Discussions
- 212 Industry Talk
- 442 Announcements
- 4.6K Ideas & Feature Requests
- 140 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 449 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 302 Events
- 33 Webinars
- 7.3K Forum Archives