Multiple Results Formula

Clayton DSouza

Hello! everyone

I was wondering if someone could help me with a formula

We have an audit schedule based on 5 years and I currently have the "Count" essentially telling me how many times a topic was audited in the past 5 years.

What I would like to generate is - "in which years was the exact same topic audited?"

which would help me in building my yearly audit schedule :)

example -

Audit Topic - "Slip and Fall Program"

How many times in 5 years was this program audited - 4 times

---------------I have it figured out till here-----------------

Seeing if the below formula exists

"In which years was this topic Audited - 2020, 2018, 2016, 2021"

I would truly appreciate your help. thank you very much!

Paul Newcome

You should be able to use an NIDEX/MATCH

=INDEX({Source Sheet Previous Year Column}, MATCH(Topic@row, {Source Sheet Source Column}, 0))

Paul Newcome

Are you able to provide a screenshot of your source data?

Clayton DSouza

Sorry, I don't truly understand the question - still new to smartsheet and figuring out all the ways i could make my audit schedule better

I currently have 4 columns, which is drawing data from the Master Schedule.

Audit Plan | Audit Topic | Times Executed | Year Executed

Paul Newcome

What about the Master Schedule? How is that structured?

Clayton DSouza

below -

Audit Plan for 2019 | Audit Source | Audit Description | Previous Years Audit |

The " Previous Years" is a multi-drop down. This was all managed via Excel previously, included a lot of manual referencing back and forth to build the new schedule.

Paul Newcome

You should be able to use an NIDEX/MATCH

=INDEX({Source Sheet Previous Year Column}, MATCH(Topic@row, {Source Sheet Source Column}, 0))

Clayton DSouza

Paul,

That worked perfectly!!!

Thank you so very much

Paul Newcome

Happy to help. 👍️