1. ## 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. ## 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>

3. ## 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

4. ## 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>

5. ## Re: Lookup (2000)

Excellent-works great-Thanks!

6. ## 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. ## 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.

8. ## 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
•