Consulting

Results 1 to 4 of 4

Thread: Best way to use lookup to find last value in column of another sheet

  1. #1
    VBAX Regular
    Joined
    Mar 2020
    Location
    Ammon
    Posts
    15
    Location

    Best way to use lookup to find last value in column of another sheet

    Using a lookup function to find the last value in a column based on a value in an array where the function is written, I have used the following function:

    '=LOOKUP(2,1/($A3&"!F:F">0),$A3&"!F:F")

    A3 is the vertically anchored cell I am using that references the sheet name where I want to execute the lookup while column F stores the values that I want the function to return. The result I am receiving is, #VALUE! The question is, is there a better way to execute this procedure without drawing the error. I have attached an example file. Thanks in advance.

    Lookup Value.xlsx


    - Ben

  2. #2
    Knowledge Base Approver VBAX Wizard
    Joined
    Oct 2005
    Location
    Surrey UK
    Posts
    5,293
    In G2 copied down, try:
    =LOOKUP(2,1/(--NOT(ISBLANK(INDIRECT("'" & $A2 & "'!F1:F9999")))),INDIRECT("'" & $A2 & "'!F1:F9999"))
    this uses NOT(ISBLANK( rather than >0 in case the last value is negative.
    It also limits how many rows are taken into account using F1:F9999 rather than F:F to save on recalculation time; adjust this 9999 to a coverall value that will never be exceeded on those other sheets.
    p45cal
    Everyone: If I've helped and you can't be bothered to acknowledge it, I can't be bothered to look at further posts from you.

  3. #3
    Knowledge Base Approver VBAX Wizard
    Joined
    Apr 2012
    Posts
    5,123
    I use:

    PHP Code:
    =INDEX(OFFSET(INDIRECT(ADDRESS(1;6;1;1;A2));;;2^10);MATCH(10^8;OFFSET(INDIRECT(ADDRESS(1;6;1;1;A2));;;2^10);1)) 

  4. #4
    VBAX Regular
    Joined
    Mar 2020
    Location
    Ammon
    Posts
    15
    Location
    Thanks guys, these will help.


    - Ben

Posting Permissions

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