# Match and Index functions to create a table referencing another sheet

Options

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 😀

• Options

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

• Options

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

• ✭✭✭✭✭✭
Options

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.

• Options

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.

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

• ✭✭✭✭✭✭
Options

@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.

• Options

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.

• ✭✭✭✭✭✭
Options

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

## Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!