+ Reply to Thread
Results 1 to 5 of 5

Looking up from a dynamic array

  1. #1
    Forum Contributor
    Join Date
    06-30-2017
    Location
    Los Angeles, CA
    MS-Off Ver
    Excel 2013
    Posts
    251

    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.
    Attached Files Attached Files

  2. #2
    Forum Expert 63falcondude's Avatar
    Join Date
    08-22-2016
    Location
    USA
    MS-Off Ver
    2016
    Posts
    5,874

    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. #3
    Forum Expert 63falcondude's Avatar
    Join Date
    08-22-2016
    Location
    USA
    MS-Off Ver
    2016
    Posts
    5,874

    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. #4
    Forum Contributor
    Join Date
    06-30-2017
    Location
    Los Angeles, CA
    MS-Off Ver
    Excel 2013
    Posts
    251

    Re: Looking up from a dynamic array

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

  5. #5
    Forum Expert 63falcondude's Avatar
    Join Date
    08-22-2016
    Location
    USA
    MS-Off Ver
    2016
    Posts
    5,874

    Re: Looking up from a dynamic array

    You're welcome. Thanks for the rep!

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] Populate Dynamic Array from Cell Values and write to Dynamic Range
    By TFiske in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 03-22-2018, 09:09 AM
  2. [SOLVED] Looping thru arrays with dynamic names. Array1/2/3/5, how to array(Array & i) syntax?
    By graym463 in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 11-24-2015, 10:15 AM
  3. [SOLVED] Dynamic Changing Data, Based on Criteria Store in Array, Sum array and store in Cell
    By penbeacho in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 07-28-2015, 10:31 AM
  4. Dynamic Row Lookup Array within Array formula
    By OLLY-7 in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 07-17-2014, 04:55 PM
  5. How to Preserve a Dynamic Array When the Array is Created
    By Excel Guy 123 in forum Excel Programming / VBA / Macros
    Replies: 12
    Last Post: 05-09-2014, 12:50 PM
  6. Loop new messages containing a table, populate a dynamic array, paste array to Excel
    By laripa in forum Outlook Programming / VBA / Macros
    Replies: 1
    Last Post: 05-19-2013, 07:20 AM
  7. [SOLVED] Populate Listbox with all rows of a dynamic array where elements of a single array match.
    By Tayque_J_Holmes in forum Excel Programming / VBA / Macros
    Replies: 21
    Last Post: 08-07-2012, 04:54 AM

Bookmarks

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