Checking a box on one sheet if criteria are met in two columns on another sheet

Lindsay Volpe

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!

Jason Tarpinian

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)

Lindsay Volpe

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