+ Reply to Thread
Results 1 to 7 of 7

Dynamic Row Lookup Array within Array formula

  1. #1
    Registered User
    Join Date
    03-29-2013
    Location
    London
    MS-Off Ver
    Excel 2016
    Posts
    78

    Dynamic Row Lookup Array within Array formula

    Hi Guys,


    I am trying to use an array formula to give me all instances of matching values but where the row lookup array is dynamic and first determined by where a match is found in the headings row.

    I have an array formula working well at the moment where the row lookup array stays the same each time but I am now trying to adapt it to be dynamic and this is not working for me.


    The attached Worksheet should hopefully explain more clearly. The red text on the CRATING sheet shows how the data would look given the current data set in the BOXES sheet.


    Basically the formula in D53 on the CRATING sheet should find a match for C52 from CRATING sheet (in this example that's "Consolidation Crate 4") in the array (BOXES!AL5:BY5) and where it finds a match this will give the row lookup array - in this instance that would be (BOXES!AO6:AO26). Next the lookup value is ANY number greater than 0 (rather than a specific value). This should finally then give me the row number that I can use with the rest of the formula.

    The basic formula i have at the moment is and the underlined bits are what I need to make dynamic or change the lookup value.:
    =IFERROR(INDEX(BOXES!$A$6:$BY$26,SMALL(IF(BOXES!$AJ$6:$AJ$26=CRATING!$C$52,ROW(BOXES!$AJ$6:$AJ$26)-ROW(BOXES!$AJ$6)+1),ROWS(D$53:D53)),MATCH(D$7,BOXES!$A$5:$BY$5,0)),"")

    (Note: D$7 will actually be C52 in order to get the qty for that crate)

    Any help you be great. This is probably a bit complex given my current knowledge of array formulas but eager to learn!

    Please see attached worksheet and I'll be happy to elaborate if need be.

    Thanks as ever.

    Dynamic Row Lookup Array within Array formula.xls

  2. #2
    Forum Expert
    Join Date
    09-01-2012
    Location
    Norway
    MS-Off Ver
    Office 365
    Posts
    2,572

    Re: Dynamic Row Lookup Array within Array formula

    Try this in D53:
    Formula: copy to clipboard
    Please Login or Register  to view this content.


    Note the 0 in the INDEX function which is used to return the entire column.
    Array formula of course, Ctrl + Shift + Enter.
    <----- If you were helped by my posts you can say "Thank you" by clicking the star symbol down to the left

    If the problem is solved, finish of the thread by clicking SOLVED under Thread Tools
    I don't wish to leave you with no answer, yet I sometimes miss posts. If you feel I forgot you, remind me with a PM or just bump the thread.

  3. #3
    Registered User
    Join Date
    03-29-2013
    Location
    London
    MS-Off Ver
    Excel 2016
    Posts
    78

    Re: Dynamic Row Lookup Array within Array formula

    Hi Jacc,

    That's amazing! Works perfectly for the quantity. Than you.

    I'd be grateful if you could explain briefly how it works

    I'm also looking at it trying to see how it could be adapted to return the other pieces of relevant information but not sure on this.


    Thanks very much again.

  4. #4
    Forum Expert
    Join Date
    09-01-2012
    Location
    Norway
    MS-Off Ver
    Office 365
    Posts
    2,572

    Re: Dynamic Row Lookup Array within Array formula

    Glad to hear it works!
    I'd be happy to continue in a day or two, working 12 hour days right now.

  5. #5
    Registered User
    Join Date
    03-29-2013
    Location
    London
    MS-Off Ver
    Excel 2016
    Posts
    78

    Re: Dynamic Row Lookup Array within Array formula

    No worries.

    I'll be continuing to work on it where I'm able in the mean time.

    Look forward to hearing back when you get a mo.

  6. #6
    Registered User
    Join Date
    03-29-2013
    Location
    London
    MS-Off Ver
    Excel 2016
    Posts
    78

    Re: Dynamic Row Lookup Array within Array formula

    No worries.

    I'll be continuing to work on it where I'm able in the mean time.

    Look forward to hearing back when you get a mo.

  7. #7
    Registered User
    Join Date
    03-29-2013
    Location
    London
    MS-Off Ver
    Excel 2016
    Posts
    78

    Re: Dynamic Row Lookup Array within Array formula

    Hi Jacc,


    Have you had any thoughts on this? I have posted a separate question as appreciate your possibly busy at the mo.

    Thanks anyway.

+ 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. 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
  2. Dynamic or Variable Lookup array in INDEX, MATCH or VLOOKUP
    By kishor_c in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 09-16-2012, 09:46 AM
  3. [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
  4. Replies: 2
    Last Post: 11-05-2011, 03:26 PM
  5. Dynamic Array lookup
    By lopg2009 in forum Excel General
    Replies: 12
    Last Post: 09-18-2010, 01:41 PM

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