Results 1 to 8 of 8

Thread: Lookup (2000)

  1. #1
    Bronze Lounger
    Join Date
    Jan 2001
    Posts
    1,418
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Lookup (2000)

    Here is my task: I have a 5-column (A-E), 10-row (1-10) worksheet. I can use the Lookup function at Cell C12 to return the maximum (or minimum) value in the same column. Is there a variation of the Lookup function that will return the corresponding cell contents in say column A that corresponds to the same row where the max (or min) value is found in column C? For example, assume the Lookup function in Cell C12 returns the maximum value found in column C1-10, and this figure is located at Cell C4 . Is there a function I could insert at C13 that would automatacally give me the contents of, say, A4 (or B4, D4, E4)?
    Thanks,
    Jeff

  2. #2
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Lookup (2000)

    The following formula will return the value in A1:A10 that corresponds to the max value in C1:C10.

    <pre>=OFFSET(A1,MATCH(MAX(C1:C10),C1:C10)-1,0)
    </pre>

    Legare Coleman

  3. #3
    Bronze Lounger
    Join Date
    Jan 2001
    Posts
    1,418
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Lookup (2000)

    Legare,
    It doesn't seem to work for me. Try inserting (at E83) the date (from Column A) on which the highest NYMEX closed (from Column E). The LookupMax function is located at E82.
    Thanks,
    Jeff
    Attached Files Attached Files

  4. #4
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Lookup (2000)

    There were two problems. The first parameter to the Offset function needed to be adjusted to A5 since the Max function started in E5, and I left the third parameter to the Match function out in my first post. The corrected formula should read:

    <pre>=OFFSET(A5,MATCH(MAX(E5:E80),E5:E80,0)-1,0)
    </pre>

    Legare Coleman

  5. #5
    Bronze Lounger
    Join Date
    Jan 2001
    Posts
    1,418
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Lookup (2000)

    Excellent-works great-Thanks!

  6. #6
    Bronze Lounger
    Join Date
    Jan 2001
    Posts
    1,418
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Lookup (2000)

    Hans,
    I'm trying to use a variation of this formula in another problem, but can't seem to figure out the role of the "-1". Can you help me out with a good explanation?
    Thanks,
    Jeff

  7. #7
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Lookup (2000)

    The MATCH function returns the position of the match relative to 1. If the match is found in the first row of the array, MATCH returns 1. The OFFSET funstion is zero based. The first row is row zero, and the first column is column 0. The -1 converts MATCH's one based row number to the zero based offset that OFFSET requires.
    Legare Coleman

  8. #8
    Bronze Lounger
    Join Date
    Jan 2001
    Posts
    1,418
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Lookup (2000)

    Thanks, Legare. Interesting paradox; you would think the gurus at microsoft would maintain some consistency!

Posting Permissions

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