Results 1 to 4 of 4

Thread: OFFSET (2003)

  1. #1
    5 Star Lounger
    Join Date
    Oct 2001
    Location
    San Bernardino, California, USA
    Posts
    734
    Thanks
    0
    Thanked 0 Times in 0 Posts

    OFFSET (2003)

    I am trying to use the OFFSET function to compare two cells, find the higher value, then return the value from the cell directly above that cell. See the graphic - it contains the formula I am using to no avail. How can I do this? In this example, I would expect the OFFSET formula to return the value of "A".
    Attached Images Attached Images

  2. #2
    5 Star Lounger
    Join Date
    Aug 2004
    Location
    Connecticut, USA
    Posts
    816
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: OFFSET (2003)

    Assuming A & B are in A1 and B1. If the values are the same, the right most column will be returned.

    <table border 1><td>A</td><td>B</td><td> </td><td>5</td><td>4</td><td>=OFFSET(A2,-1,MATCH(MAX(A2:B2),A2:B2)-1,1,1)</td></table>

  3. #3
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: OFFSET (2003)

    Don,
    I wouldn't use OFFSET for that, I would use:
    <code>=INDEX(B4:C4,,MATCH(MAX(B5:C5),B5:C5,0))</code>
    HTH
    Regards,
    Rory

    Microsoft MVP - Excel

  4. #4
    5 Star Lounger
    Join Date
    Oct 2001
    Location
    San Bernardino, California, USA
    Posts
    734
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: OFFSET (2003)

    Thanks to all. The offset/match seemed to fit my needs the best. Powerful combination.

Posting Permissions

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