Cell identification for a 'lookup'

[tx12345]

Hi

The lookup function doesn't seem to do the trick, as it depends on
having the column in which the sought after criterion is kept be sorted
in order to display the data from a related cell from an adjoining
column on the same row.

But I don't want to add a sort routine. too much trouble, too much
time.

So what i want to do is (in my case) first of all find the max value in
a particular range of cells in a particular column, then tell me which
row that value is in. From there I suppose i can just concatebate that
number with the letter of the column where the associated value is I
want displayed.


Date --------high
1/2/00------250
1/3/00------175
1/4/00------415


find the max value in the 'high' column, and tell me what row that
value is in, so i can force excel to tell me the related date in the
date column

Many thanks!

tx

 

[Arvi Laanemets]

Hi

=VLOOKUP(MAX($A$1:$A$1000),$A$1:$D$1000,2,0)

 

[Guest]

Assuming data in Columns A & B, row 1 as heading:

=INDEX($A$2:$A$4,MATCH(MAX($B$2:$B$4),$B$2:$B$4,0),1)

will give date

HTH

 

[Arvi Laanemets]

Hi again

Sorry, I didn't see that the return value is in column left to lookup
column. The VLOOKUP needs the lookup column to be leftmost in lookup range.
You have to redesign your sheet, or you have to use more complex formula,
like

=INDEX($A$1:$A$1000,MATCH(MAX($B$1:$B$1000),$B$1:$B$1000,0),)

 

[tx12345]

Thanks, the index/match formula works great.

tx