Help with IF/Index/Match Statement

Dan Walsh

Good day, Struggling trying to get this to work.

I need to track if our sites have had an audit during a particular year.

Sheet 1 "Site Audit" and contains site audit information. Mainly the site name and the date it was audited is the only information I need. I created a helper column to extract just the year

Site | Date of Audit | Helper Column

ABC | 02/15/2020 | 2020

DEF | 007/25/2020 | 2020

Sheet 2 :Site Audit Tracker" has a list of all of the facilities with columns representing a given year. My "want" it to have a "check" to represent if the site was visited during that respective year.

Site | 2020 | 2021

ABC | 1 |

DEF | 1 |

I have been able to successfully pull the year in the 2020 column by using the Index/Match using the following formula.

=INDEX({Site:Site}, MATCH(Site@row, {Helper Column}, 0))

So now it looks like:

Site | 2020 | 2021

ABC | 2020 |

DEF | 2020 |

But stuck on how to make it a true/false (0,1). I figured there is a IF function in this process somewhere, but can't figure out how to successfully implement it. (I realize each respective Year column will need its own formula - Im ok with that)

Garrett Henke

Hey @Dan Walsh!

I believe this will help accomplish what you are trying to do

=IF(ISDATE(INDEX({Site:Site}, MATCH(Site@row, {Helper Column},0))), 1 , 0)

Dan Walsh

Unfortunately it didn't work.

I think it is because I need it to look specifically for 2021 in the helper column on Sheet 1. If I have another entry for the same site, but a different year, it doesn't compute.

So Layman's term for column 2020 or 2021 in Sheet 2

If the Site matches in Sheet 1 and 2, put a check mark in the column 2020 if the audit was completed in the year 2020

Likewise, if the audit was completed in the year 2021, then but a check mark in the column 2021 for the respective site.

And to clarify, Sheet 1 can have multiple entries for each site for example:

Site | Date of Audit | Helper Column

ABC | 02/15/2020 | 2020

DEF | 07/25/2020 | 2020

ABC | 08/25/2021 | 2021

Sorry if this is confusing, Its confusing to me as I type it. I appreciate the time you are taking to assist.

Garrett Henke

@Dan Walsh, there are two ways to do this, one will give you the count of how many times a site was visited and the second uses the same formula combined with an IF statement to make it a checkbox.

To get the count of how many times a site was visited use the following formula: =COUNTIFS({Helper Column}, 2020, {Site}, Site@row) . Change the year as needed

To convert into a checkbox use the following formula: =IF(COUNTIFS({Helper Column}, 2020, {Site}, Site@row)>0,1,0)

Site Audit Sheet:

Site Audit Tracker to find count

Site Audit Tracker with checkbox

Dan Walsh

You..my friend, are a genius!

That is exactly what I was looking for! Thank you very much.