Help with IF/Index/Match Statement

Options

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)

Answers

  • Garrett Henke
    Garrett Henke ✭✭✭✭✭
    edited 07/14/21
    Options

    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
    Dan Walsh
    edited 07/14/21
    Options

    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
    Garrett Henke ✭✭✭✭✭
    edited 07/14/21
    Options


    @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
    Dan Walsh
    edited 07/14/21
    Options

    You..my friend, are a genius!

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

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!