Index column with multiple criteria
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?
Best Answer
-
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!
If my response was helpful in any way (or answered your question) please be sure to upvote it, mark it as awesome, or mark it as the accepted answer!
I'm always looking to connect with other industry professionals, feel free to connect with me on LinkedIn as well!
Answers
-
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!
If my response was helpful in any way (or answered your question) please be sure to upvote it, mark it as awesome, or mark it as the accepted answer!
I'm always looking to connect with other industry professionals, feel free to connect with me on LinkedIn as well!
-
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))
-
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!
If my response was helpful in any way (or answered your question) please be sure to upvote it, mark it as awesome, or mark it as the accepted answer!
I'm always looking to connect with other industry professionals, feel free to connect with me on LinkedIn as well!
-
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.
-
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!
If my response was helpful in any way (or answered your question) please be sure to upvote it, mark it as awesome, or mark it as the accepted answer!
I'm always looking to connect with other industry professionals, feel free to connect with me on LinkedIn as well!
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.8K Get Help
- 376 Global Discussions
- 207 Industry Talk
- 440 Announcements
- 4.5K Ideas & Feature Requests
- 139 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 449 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 284 Events
- 33 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!