Possible COLLECT or INDEX/MATCH IF between two sheets

alexis.ray89371

Hi,

I'm having difficulty figuring out a formula that references two different sheets. Any help would be much appreciated! Thank you!

Below are screenshots of the two different sheets along with notes/explanations.

Below is the text of the sheet names and fields for easy reference on formula ideas. That way you don't have to type out manually.

eMERGE Asset Staging Table- OFFLINE

Equipment Name

2020-2021 Status

Asset Condition Assessments- Active- OFFLINE

Asset

Condition Assessment Year

Paul Newcome

I am actually going to suggest an IF/COUNTIFS to first count how many rows contain that particular combination and then say if that is greater than zero (meaning at least one row was found) then output "Complete".

=IF(COUNTIFS({Other Sheet Asset Column}, @cell = [Equipment Name]@row, {Other Sheet Year Column}, @cell = "2020-2021") > 0, "Complete")

Paul Newcome

I am actually going to suggest an IF/COUNTIFS to first count how many rows contain that particular combination and then say if that is greater than zero (meaning at least one row was found) then output "Complete".

=IF(COUNTIFS({Other Sheet Asset Column}, @cell = [Equipment Name]@row, {Other Sheet Year Column}, @cell = "2020-2021") > 0, "Complete")

alexis.ray89371

Wow! You are a genius. Thank you, it worked. 😁

Paul Newcome

Happy to help. 👍️