Index Collect with a Date Range Criterion

Andy_B

I am trying to utilize an index collect formula that will cross-reference another sheet. Some of the reference criteria are numbers but two criteria need to narrow the options to a specific date range (i.e. >= DATE1 and < =DATE2). These columns are in date format. The data I am trying to bring back into the cell is a name in text format. The cell with the formula is in text/number format.

This is what my formula looks like currently, but I am getting a #INVALID VALUE error.

=INDEX(COLLECT({Name}, {ID #}, =[ID #], {DATE1}, >=DATE@row, {DATE2}, <=DATE@row), 1)

I was able to include a date criterion in a different index/collect formula elsewhere in the sheet, but that formula was in a date column type. Is there a way to make this work in a text/number formatted column?

Matt Lynn#

@Andy_B A couple things:
1.) You shouldn't need the "=" sign with the [ID #]… and is that supposed to be [ID #]@row? Seems like the @row is missing or some other indicator as to what cell from the ID # column it's looking at.
2.) Also, if your date column in the source sheet has any blanks it can pose a problem. You would want to reference it within an iferror().

So something similar to:
=INDEX(COLLECT({Name}, {ID #}, [ID #]@row, {DATE1}, >=DATE@row, {DATE2}, <=DATE@row), 1)

If that don't work play with adding the iferror around the source reference…. you prob don't need it on this one. Just something to keep in mind for ref dates, especially if you're pulling months/years from that column ref.