+ Reply to Thread
Results 1 to 6 of 6

Inserting a Function Into Array Declaration

Hybrid View

  1. 06-16-2016, 04:48 PM #1
    Bobby Smith
    Bobby Smith is offline
    Registered User
    Join Date
    06-02-2016
    Location
    Pittsburgh
    MS-Off Ver
    MS 2010
    Posts
    17

    Inserting a Function Into Array Declaration

    Hi,

    I am trying to use LOOKUP to find the last instance of a value in a list that will grow to an undefined length, this is the formula I'm using:

    =LOOKUP(2,1/($C5:$C(last_row)=A1),$D5:$D(last_row))

    last_row is a MATCH function (that works) that reads down until the first blank cell and returns the length of the list, this list could be 10 one day and 100 the next, it's impossible to know how long it is.

    The problem with the LOOKUP function is here: $C5:$C(last_row) Excel doesn't seem to like that I use last_row in the array declaration even though it returns a number, is there a way to reconcile this?

    ~ bobby
    Reply With QuoteRegister To Reply
  2. 06-16-2016, 04:56 PM #2
    Jdevil
    Jdevil is offline
    Forum Contributor
    Join Date
    07-13-2015
    Location
    Quebec, Canada
    MS-Off Ver
    2013
    Posts
    232

    Re: Inserting a Function Into Array Declaration

    nevermind.
    Reply With QuoteRegister To Reply
  3. 06-16-2016, 05:02 PM #3
    TMS
    TMS is offline
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    48,946

    Re: Inserting a Function Into Array Declaration

    One way:
    Formula: copy to clipboard 
    =LOOKUP(2,1/(INDIRECT("$C5:$C$" &last_row)=A1),INDIRECT("$D5:$D$" & last_row))
    Trevor Shuttleworth - Retired Excel/VBA Consultant

    I dream of a better world where chickens can cross the road without having their motives questioned

    'Being unapologetic means never having to say you're sorry' John Cooper Clarke

    Reply With QuoteRegister To Reply
  4. 06-17-2016, 08:51 AM #4
    Bobby Smith
    Bobby Smith is offline
    Registered User
    Join Date
    06-02-2016
    Location
    Pittsburgh
    MS-Off Ver
    MS 2010
    Posts
    17

    Re: Inserting a Function Into Array Declaration

    Thanks Trevor, works great.

    ~ bobby
    Reply With QuoteRegister To Reply
  5. 06-17-2016, 08:55 AM #5
    MarvinP
    MarvinP is offline
    Forum Guru MarvinP's Avatar
    Join Date
    07-23-2010
    Location
    Woodinville, WA
    MS-Off Ver
    Office 365
    Posts
    16,445

    Re: Inserting a Function Into Array Declaration

    Hi Bobby,

    If you don't know how big your array will be, you can use a Dynamic Named Range for it.

    http://www.bettersolutions.com/excel...G820716330.htm
    http://www.vertex42.com/ExcelArticle...ed-ranges.html
    One test is worth a thousand opinions.
    Click the * Add Reputation below to say thanks.
    Reply With QuoteRegister To Reply
  6. 06-17-2016, 09:00 AM #6
    TMS
    TMS is offline
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    48,946

    Re: Inserting a Function Into Array Declaration

    You're welcome.



    If you are satisfied with the solution(s) provided, please mark your thread as Solved.


    New quick method:
    Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.

    Or you can use this way:

    How to mark a thread Solved
    Go to the first post
    Click edit
    Click Go Advanced
    Just below the word Title you will see a dropdown with the word No prefix.
    Change to Solved
    Click Save


    You may also want to consider thanking those people who helped you by clicking on the little star at the bottom left of their reply to your question.
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. OLE Object Array Declaration Syntax Problem
    By beat in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 04-26-2013, 08:11 AM
  2. passing a variable to an array declaration
    By labogola in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 01-09-2012, 04:10 AM
  3. Initial Declaration of Non-Static Array in Function
    By Extradimensional in forum Excel Programming / VBA / Macros
    Replies: 13
    Last Post: 09-11-2011, 09:22 PM
  4. Range as Parameter in Function Declaration
    By Hot Soup in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 08-03-2009, 05:51 PM
  5. Default value/text for variable in Function declaration
    By leecs in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 02-27-2008, 10:07 PM
  6. Array dimension declaration
    By yellephant in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 06-29-2007, 08:44 AM
  7. DLL function Declaration
    By rkappa in forum Excel General
    Replies: 0
    Last Post: 03-14-2007, 10:25 AM
  8. dynamic initialisation/declaration of array using an index
    By [email protected] in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 05-18-2005, 11:42 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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1