+ Reply to Thread
Results 1 to 4 of 4

LOOKUP with OFFSET?

  1. #1
    Registered User
    Join Date
    01-20-2005
    Posts
    22

    LOOKUP with OFFSET?

    Hi all,
    I have a formula which returns the last/bottom value in a column (in this case column E on sheet 'Data Set'):
    =LOOKUP(9.99999999999999E+307,'Data Set'!E:E)

    Is it possible to have something to retrieve the value above it? (i.e. the last but one) - I thought I might be able to do it with OFFSETs but can't get it to work.

    Thanks

    Robert

  2. #2
    Forum Expert
    Join Date
    12-24-2004
    Location
    Sweden
    Posts
    1,256
    One option is to use MATCH.

    =INDEX(A1:A20,MATCH(9999,A1:A20,1)-1)
    MATCH will find the position of the last value. And -1 will find the 2nd last value.

    Ola Sandström

  3. #3
    Registered User
    Join Date
    01-20-2005
    Posts
    22
    Excellent, and it works!
    Thanks v. much.

    Robert

  4. #4
    Forum Expert
    Join Date
    12-24-2004
    Location
    Sweden
    Posts
    1,256
    Happy it worked, and thanx for the feedback.
    Ola Sandström

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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