Index column with multiple criteria

ChiEze

I have two "Slack Additions" and "Todays Location".

Slack Additions is populated from a Slack Workflow. It is roster of peoples names, location, location start date and location end date. Names can appear on this sheet multiple times, with different locations for different time periods.

I am trying to write a formula in "Todays Location" where a name only appears once but the location updates dynamically based on a formula that indexes the names from Sheet A and calls the location based on if todays date falls between the location start and end date.

Slack Additions:

Todays Location:

So far, I've tried using Index Collect but it keeps returning an invalid value.

=INDEX(COLLECT({A-Slack Additions Range 4}, {A-Slack Additions Range 2}, TODAY() >= {A-Slack Additions Range 2}, {A-Slack Additions Range 3}, TODAY() <= {A-Slack Additions Range 3}), 1)

What am I doing wrong?

bisaacs

Hey @ChiEze,

I wrapped what you had in a IF/MATCH formula to search for the name match first:

=IF(MATCH(Name@row, Name1:Name3), INDEX(COLLECT([Where Are You]1:[Where Are You]3, [Location End Date]1:[Location End Date]3, >=TODAY(), [Location Start Date]1:[Location Start Date]3, <=TODAY()), 1))

Here's the sheet I used this on (with the formula being located in the "Today's Location" column):

It first looks for the Name in the row to match the group of 3 names, then it uses the INDEX/COLLECT formula to search for the results. I think part of your issue was in the "criteria" portion of the COLLECT function you were entering excess info. COLLECT already asks for the criteria range in the formula, you just have to provide the criteria:

=COLLECT([range to collect], [range you are evaluating with the criteria], [criteria range condition])

Hope this helps!

bisaacs

Hey @ChiEze,

I wrapped what you had in a IF/MATCH formula to search for the name match first:

=IF(MATCH(Name@row, Name1:Name3), INDEX(COLLECT([Where Are You]1:[Where Are You]3, [Location End Date]1:[Location End Date]3, >=TODAY(), [Location Start Date]1:[Location Start Date]3, <=TODAY()), 1))

Here's the sheet I used this on (with the formula being located in the "Today's Location" column):

It first looks for the Name in the row to match the group of 3 names, then it uses the INDEX/COLLECT formula to search for the results. I think part of your issue was in the "criteria" portion of the COLLECT function you were entering excess info. COLLECT already asks for the criteria range in the formula, you just have to provide the criteria:

=COLLECT([range to collect], [range you are evaluating with the criteria], [criteria range condition])

Hope this helps!

ChiEze

Hi @bisaacs thank you for this! I reworked it to match what I want and still got nothing. The list it is indexing lives in another sheet, they're not on the same sheet. The way you've written it, they are both on the same sheet. This is what I used and still unparseable. Formulas are not my strong suit, can you walk me through it?

=IF(MATCH(Name@row, {Slack Additions Name}), INDEX(COLLECT({Slack Additions Where Aare You}, {Slack Additions Location End Date}, >=TODAY(), {Slack Additions Location Start Date}, <=TODAY()), 1))

bisaacs

Hey @ChiEze,

Unfortunately I can't really help you with the reference ranges in your formula as it'll be specific to the sheet you're trying to reference, and the formula you provided doesn't actually show the range you're trying to calculate. I would try to make sure that in your references you are searching the whole column (so for "Name" reference, make sure the sheet reference is pulling Name:Name, for "Where Are You", the reference is pulling [Where Are You]:[Where Are You], etc).

Are you getting an #unparseable or #invalid value? If it's returning #unparseable then the formula is wrong, if it's returning #invalid value then the formula can't find data that matches the criteria.

Hope this helps!

ChiEze

It worked! Turns out I forgot to change the ate columns to Date type. That fixed it. Thanks!!

Edit: It did not work. It calls the first date match regardless of name.

bisaacs

Hey @ChiEze,

So you were actually close with your initial formula, it was just how you entered the criteria that was causing it to throw an error. The trouble was if there were no dates that matched the criteria, then it would throw the "INVALID VALUE" error to you. To fix that, wrap your formula in an IFERROR function:

=IFERROR(INDEX(COLLECT({A-Slack Additions Range 4}, {A-Slack Additions Range 2}, < = TODAY(), {A-Slack Additions Range 3}, >=TODAY()), 1), "No locations found")

Hope this one works!