Match and Index functions to create a table referencing another sheet

HR

Hello,

I need help creating a formula which will work.

Sheet 1 - contains my data

Sheet 2 - Contains the tracking matrix I am trying to create

I need a formula which will look at sheet 1, determine if an inspection has taken place within a certain month, and then return the date of that inspection into sheet 2.

I've been told to use INDEX or MATCH formulas, but have no idea how that would be constructed.

Help greatly appreciated 😀

Lawrence West

I looked at your examples and I think your going to need a COUNTIFS formula to do what your looking for. I created a quick example and attached a published link below to you an idea. The Upper case A,B,C adds the number of inspections month after month and the lower case a,b,c just gives the number of inspections for that month. I hope this helps.

https://publish.smartsheet.com/d3ddf296a3ab4741930dcdd62291d018

HR

Thank you very much Lawrence, unfortunately when I click on your example sheet I can only view data, I cannot see any formulas.

Paul Newcome

Try something along the lines of...

=INDEX(COLLECT({Form Sheet Date Column}, {Form Sheet Location Column}, $A@row, {Form Sheet Date Column}, AND(@cell >= E$1, @cell <= E$2)), 1)

If you put the above in E5, you can dragfill to down and to the right to have it automatically look at the dates and locations on your matrix sheet.

Lawrence West

The formula I used:

=COUNTIFS($Location$1:$Location$12, $[Location Arrangement]1, $Month$1:$Month$12, 3)

I also made a change to the published sheet and you should be able to go through the formulas.

https://app.smartsheet.com/b/publish?EQBCT=0f299867781d42deb1a6e3d6731bb0ea

Additionally, Paul's suggestion may be a better solution.

Paul Newcome

@Lawrence West The reason I used the formula that I did is because we are wanting to pull a date and not necessarily a count.

Lawrence West

Paul Newcome

I missed the fact that a date was needed. I thought they wanted a count of inspections. Thanks for clearing that up.

HR

I missed the fact that you wanted to pull the date. I thought you wanted a count of inspections. My formula does not work for your needs. Sorry for the confusion.

Paul Newcome

@Lawrence West No worries. I've done the same thing plenty of times before.