Conditional Index Match with Date => TODAY

MeganLucy

Hello! I'm stumped on how to do a conditional index match (or index collect) between two sheets. I'd like to pull in a budget number from another sheet (based off of the project's acronym) if and only if that budget number's end date is in the past. The sheet that I am indexing into will only have the project's acronym once, but the external "other sheet" I'm referencing would have it listed multiple times. (Hence the conditional regarding the budget number end date.)

Here's the formula I have so far:

=INDEX(COLLECT({Other Sheet Budget Number}, {Other Sheet Project Acronym}, [Project Acronym]@row),{Other Sheet Budget Number End Date}, >=TODAY()), 1)

But I keep getting the #UNPARSEABLE error. I believe this is because of the {Other Sheet Budget Number End Date}, >=TODAY()) portion and an issue with referencing a range to a single value, but I'm not entirely sure.

Any advice? Should I be working an IF formula in somewhere?

Thank you!

Genevieve P.

Hi @MeganLucy

It actually looks like you have an extra closing parentheses ) in there, which is causing your error.

You'll want to ensure you're not adding in any of these ) until the very end of the formula:

=INDEX(COLLECT(formula), 1)

So in your case:

=INDEX(COLLECT({Other Sheet Budget Number}, {Other Sheet Project Acronym}, [Project Acronym]@row, {Other Sheet Budget Number End Date}, >=TODAY()), 1)

Let me know if this resolved your issue!

Cheers,

Genevieve