Looking up from a dynamic array

1. Looking up from a dynamic array

Hi,

The attached sheet has two tabs, one is an output tab and one is a source tab ("Holdings"). I'm trying to populate Ticker and Portfolio Weighting % on the Main_Output tab, but only for the portion of the Holdings tab where column L = A10 on the Main Output tab.

I realize that I could make a pivot table from the Holdings tab and copy and paste accordingly to the Main Output tab (which is the backup plan), but my goal was to make this sheet a little more dynamic (perhaps with returning an array on the Main Output tab). In any case, I've already copied and pasted what I'm looking for on the Main Output tab for the example ID GNA1K found in column L on the Holdings tab.

Also keep in mind that the sizing of the portfolio ID range can change (in certain cases maybe the GNA1K count will be 45, in others it might be 55).

I'm hoping at the very least my explanation makes sense. I didn't think this was too difficult until I actually tried doing it and now I'm not sure how to really proceed. Any help appreciated.

Thanks.

2. Re: Looking up from a dynamic array

Here's how I would tackle this:

C9 =IFERROR(INDEX(Holdings!A:A,SMALL(IF(Holdings!\$L\$2:\$L\$880=\$A\$10,ROW(Holdings!\$L\$2:\$L\$880)),ROWS(\$1:1))),"") Ctrl Shift Enter

Drag through E9 then down as far as needed.

3. Re: Looking up from a dynamic array

Another (more efficient) option would be to enter the formula from post #2 into C9 and then this in D9:

=IF(C9="","",INDEX(Holdings!B:B,MATCH(\$C9,Holdings!\$A:\$A,0)))

Drag the D9 formula through E9 then drag the formulas in C9:E9 down as far as needed.

4. Re: Looking up from a dynamic array

Thank you so much, believe this is what I'm looking for.

5. Re: Looking up from a dynamic array

You're welcome. Thanks for the rep!

There are currently 1 users browsing this thread. (0 members and 1 guests)

Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1