+ Reply to Thread
Results 1 to 4 of 4

nested formula

  1. #1
    Registered User
    Join Date
    05-04-2007
    Posts
    2

    nested formula

    Hello, I'm stuck on stupid little formula. I am trying to refrence a specific cell and can't figure out how to do it. I know that the cell i want to refrence is offset 3 columns from the largest date value in another column. i am trying to use the LARGE function to find the largest date value inside the offset function but can't figure it out. Can anyone tell me how to do this right or an easier way of going about it??
    Thanks
    Justin

  2. #2
    Forum Expert sweep's Avatar
    Join Date
    04-03-2007
    Location
    Great Sankey, Warrington, UK
    MS-Off Ver
    2003 / 2007 / 2010 / 2016 / 365
    Posts
    3,454
    Hi,

    Possible easier to use index and match?

    =INDEX(range_to_be_returned,MATCH(MAX(lookup_range),lookup_range,0),1)
    Rule 1: Never merge cells
    Rule 2: See rule 1

    "Tomorrow I'm going to be famous. All I need is a tennis racket and a hat".

  3. #3
    Registered User
    Join Date
    05-04-2007
    Posts
    2
    For future work I would rather go about it using the offset and large functions. this is what i have right now =OFFSET(LARGE('West Pannel'!A7:A35,1),0,3,0,0).

  4. #4
    Forum Expert
    Join Date
    01-12-2007
    Location
    New Jersey
    Posts
    2,127
    Just to add to sweep's suggestion, try this:

    =OFFSET(INDEX(ARRAY,MATCH(MAX(LOOKUP_RANGE),LOOKUP_RANGE,0)),0,3)

    That will return a value in the same row, 3 columns to the right, from the largest value.

+ 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