INDEXING and Validating

I have a perplexing question, that I am sure someone knows and I am missing here.

I am working on a sheet to gather data from a form. However, this form will get quite lengthily and may span multiple years. With that said this is where I am stumped.

=INDEX(COLLECT({Test Sheet 1 Range 1}, {Test Sheet 1 Range 1}, <> ""), Index@row)

This formula works great. But needs to look at 2 columns before collecting. I have a month only row and a year only row that pulls that data from the create date. What I need it to do is validate the month and year before it collects it and places the data on the correct sheet. I saw some posts about using MATCH function, but nothing was close to what I needed to figure it out.

I want to make sure that when it checks the Month and year it is polling the correct data and not duplicating something for September 2024 with September 2023 data. Thanks for any assistance.


Range 1 houses data about someone

Range 2 houses the Month

Range 3 houses the year

This is what I came up with, but since I am here, it's obvious it does not work.

=INDEX(COLLECT({Test Sheet 1 Range 1}, {Test Sheet 1 Range 2} = 9, {Test Sheet 1 Range 3} = 2023), Index@row)

Best Answer

  • JamesB
    JamesB ✭✭✭✭✭✭
    Answer ✓

    @Frank Hammond

    Your formula is close, looks like you just need commas between your criterion range and criterion.

    =INDEX(COLLECT({Test Sheet 1 Range 1}, {Test Sheet 1 Range 2}, = 9, {Test Sheet 1 Range 3}, = 2023), Index@row)


Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!