INDEXING and Validating

Frank Hammond

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.

Example.

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)

JamesB

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

JamesB

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

Frank Hammond

Thank you so much, James! I knew I missing something. Now everything works as intended with my IFERROR function.

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

JamesB

Your Welcome