Index(Collect( Formula Help

SergeantPup

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?

SergeantPup

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.

Nick Korna

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).

SergeantPup

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.

SergeantPup

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.

Genevieve P.

Hey @SergeantPup, thanks for the tag! I'm glad to see that you were able to find the full formula that works for you 🙂