+ Reply to Thread
Results 1 to 5 of 5

Can I make the row lookup array/range part in an array formula variable?

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

    Can I make the row lookup array/range part in an array formula variable?

    Is it possible to make the row lookup array/range part of this formula variable so that it is first defined by finding a match in a headings column?

    =IFERROR(INDEX(BOXES!$A$6:$BY$26,SMALL(IF(BOXES!$AL$6:$AL$26>0,ROW(BOXES!$AL$6:$AL$26)-ROW(BOXES!$AL$6)+1),ROWS(E51:E$51)),MATCH(E$5,BOXES!$A$5:$BY$5,0)),0)


    I.e. The BOXES!$AL$6:$AL$26 and BOXES!$AL$6 bits would become for example BOXES!$AM$6:$AM$26 and BOXES!$AM$6 if a match was first found in AM5 (in range AL5 to BY5).


    Can't see anything online like this so all help will be great!


    Thanks.

  2. #2
    Forum Guru Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: Can I make the row lookup array/range part in an array formula variable?

    Can you post a SMALL sample file that shows us what results you expect?

    A SMALL file will have about 20 rows an about 10 columns worth of data.

    We should not have to scroll to see/find/look at the data.
    Biff
    Microsoft MVP Excel
    Keep It Simple Stupid

    Let's Go Pens. We Want The Cup.

  3. #3
    Forum Expert Vikas_Gautam's Avatar
    Join Date
    06-04-2013
    Location
    Ludhiana,Punjab, India
    MS-Off Ver
    Excel 2013
    Posts
    1,850

    Re: Can I make the row lookup array/range part in an array formula variable?

    Try this..

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


    The purpose of ROW(BOXES!$AL$6:$AL$26)-ROW(BOXES!$AL$5) is to provide you the row no..
    It has nothing to do with the result....

    Provide a sample worksheet ..
    May be we can help you better..


    Don't forget to click *

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

    Re: Can I make the row lookup array/range part in an array formula variable?

    Hi guys,


    please see attached an example worksheet which just shows the bare essentials as much as I can reduce them down to.

    Sheet 'BOXES' is the source data. Sheet 'CRATING' is where the data needs to end up. The CRATING sheet is currently using the formula I have and does show how the data should be entered however I am having to manually adjust the row lookup ranges for each new 'Crate Consolidation' section in order to use the right column on the BOXES sheet for the row lookup array.

    On my actual sheet I am going to have 40 'Crate Consolidations' which is why I want to find a way to select the appropriate column to use as the row look up array based on match in the headings range BOXES!M2:P2.


    Happy to elaborate if need be and thanks.




    Thanks.

    [ATTACH]333175[/ATTACH

  5. #5
    Forum Guru Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: Can I make the row lookup array/range part in an array formula variable?

    You currently have this...

    Data Range
    A
    B
    5
    Consolidation Crate 1
    ------
    6
    2
    7
    1
    8
    0
    9
    0
    10
    0
    11
    12
    13
    Consolidation Crate 2
    14
    2
    15
    2
    16
    0
    17
    0
    18
    0


    If you did this...

    Data Range
    A
    B
    5
    ------
    6
    Consolidation Crate 1
    2
    7
    Consolidation Crate 1
    1
    8
    Consolidation Crate 1
    0
    9
    Consolidation Crate 1
    0
    10
    Consolidation Crate 1
    0
    11
    12
    13
    14
    Consolidation Crate 2
    2
    15
    Consolidation Crate 2
    2
    16
    Consolidation Crate 2
    0
    17
    Consolidation Crate 2
    0
    18
    Consolidation Crate 2
    0


    Then you could use an INDEX/MATCH to get the range you need.

    Are you open to that idea?

+ 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] Formula Array with a variable range
    By KParkerTX in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 10-01-2013, 04:25 PM
  2. [SOLVED] Lookup question matching part of lookup value in the table array
    By kosmo in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 09-05-2013, 11:07 AM
  3. Make a Date variable equal a single-cell array formula
    By Shamala in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 03-07-2012, 06:29 PM
  4. Replies: 2
    Last Post: 11-05-2011, 03:26 PM
  5. Does filling part of an array from a range re-dimension the array?
    By barryleajo in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 08-04-2011, 10:09 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