Index(Collect( Formula Help
Hi,
@Genevieve P. appears versed in this issue from the previous posts I've read.
I've read several posts from 2020-2021 about how to do what I'm trying to do but I can't get it right and I'm hoping somebody can easily fill in the blanks of this formula. I essentially want to VLookup based on the value in 2 columns. I've read over and over again you can't do that and to instead use Index(Collect). OK, I'm to that part and I'm still receiving errors and I know I'm just putting something in the wrong order. Can somebody review and tell me what I'm doing wrong please?
Here's the Vlookup formula that works but is only comparing 1 column: =VLOOKUP([Team Name]@row, {ADO Calendar Capacity and PTO}, 13, false). I'm providing this to show it works and everyone before me asking this same question provided theirs.
What I need to do is compare the team name and the iteration name between both sheets and then return the Team Capacity Points.
With the function of an Index(collect, I know the first part should be the desired field to pull back, but I'm confused on the rest.
I need the [Team Capacity Points]@row from another sheet {ADO Calendar Capacity and PTO} and to find that row by getting an exact match of these columns [Iteration Name]@row:[Team Name]@row in both sheets (yes they're named the same between the sheets).
My best attempt at this has been 50 versions of this: =INDEX(COLLECT([Team Capacity Points]@row, {ADO Calendar Capacity and PTO}, [Iteration Name]@row:[Team Name]@row)). I either get circular references or other errors. Can somebody help me put this in the correct order to work please?
Best Answer
-
actually, I confirmed I just got it by trying to retype what you suggested: =INDEX(COLLECT({Team Capacity Points}, {Iteration Name}, [Iteration Name]@row, {Team Name}, [Team Name]@row), 1). Now that I have the structure, I was able to add another column to reverse the flow of info back to the other sheet. This is exactly what I needed and will allow me to do so much more. Thank you.
Answers
-
Hi @SergeantPup,
It should be something like this, using the (hopefully!) relevant column names in the cross sheet references:
=INDEX(COLLECT({Team Capacity Points}, {Iteration Name}, [Iteration Name]@row, {Team Name}, [Team Name]@row),1)
This is relying on you only having a single entry of team capacity points against each Iteration/Team name combo. If you have multiple options for these, and want a particular one you would need something to get the relevant value (for example a MAX COLLECT to get the latest date in place of the row number).
-
That gets me closer but still an invalid error. I added a reference for the Team Name because I thought your suggestion was missing it. This is what I came up with:
=INDEX(COLLECT({Team Capacity Points}, {Iteration Name}, {Team Name}, [Iteration Name]@row:[Team Name]@row), 1)
{Team Capacity Points} references the field I want to pull in from another sheet
{Iteration Name} references the Iteration Name from the sheet I want to pull from and must match the current sheet row
{Team Name} references the Team Name from the sheet I want to pull from and must match the current sheet row
[Iteration Name]@row:[Team Name]@row) represents the two columns in the current sheet that I want matched to the other sheet to pull back the {Team Capacity Points}
If it needs to be one reference just let me know. It seemed like from your suggestion, they were broke out individually.
There's no danger of cross pollination. Both sheets only have 1 iteration/1 team name value so there should always only be 1 match for each.
-
actually, I confirmed I just got it by trying to retype what you suggested: =INDEX(COLLECT({Team Capacity Points}, {Iteration Name}, [Iteration Name]@row, {Team Name}, [Team Name]@row), 1). Now that I have the structure, I was able to add another column to reverse the flow of info back to the other sheet. This is exactly what I needed and will allow me to do so much more. Thank you.
-
Hey @SergeantPup, thanks for the tag! I'm glad to see that you were able to find the full formula that works for you 🙂
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63K Get Help
- 380 Global Discussions
- 212 Industry Talk
- 442 Announcements
- 4.6K Ideas & Feature Requests
- 140 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 449 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 305 Events
- 34 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!