IF with INDEX MATCH

reece.buckner

Hello,

I would like to check a box on one sheet, if a box is checked on a different sheet, matching certain criteria.

This is what I have so far:

=IF(INDEX({Current Estimates Range 1}, MATCH([Project Name]@row, {Current Estimates Range 2})) = 1, 1)

If the Takeoff Information Column is checked on this sheet,

then check the box on this other sheet

The ultimate goal is to be able to build an automation on the second sheet to request a row update if the box is checked on the first sheet.

Genevieve P.

Hi @reece.buckner

This looks like a good start! However it looks like you have multiple rows with the same matching criteria (the Project Name) so the formula doesn't know what row you're looking for.

What I would actually suggest doing is use a COUNTIFS Function (plural) instead. This will count how many rows in your first sheet match your criteria, and it makes it really easy to add in more criteria down the line. Then have your IF statement around this.

For example:

=IF(COUNTIFS({Current Estimates Range 1}, 1, {Current Estimates Range 2}, [Project Name]@row) > 0, 1)

If even one row in your first sheet is checked, the COUNT will be greater than 0 so it will check the box. If 10 rows are checked, again, it will check the box.

If you had other things to filter by (e.g. only for rows with a "Green" status), then you just have to throw in the new {range} and "Criteria":

=IF(COUNTIFS({Current Estimates Range 1}, 1, {Current Estimates Range 2}, [Project Name]@row, {Status Column}, "Green") > 0, 1)

Let me know if this makes sense and will work for you!

Cheers,

Genevieve