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)


--
Arvi Laanemets
( My real mail address: arvi.laanemets<at>tarkon.ee )



"tx12345" <tx12345.2bwjoo_1154504408.1288@excelforum-nospam.com> wrote in
message news:tx12345.2bwjoo_1154504408.1288@excelforum-nospam.com...
>
> 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
>
>
> --
> tx12345
> ------------------------------------------------------------------------
> tx12345's Profile:
> http://www.excelforum.com/member.php...o&userid=24776
> View this thread: http://www.excelforum.com/showthread...hreadid=567345
>

[Toppers]

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

"tx12345" wrote:

>
> 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
>
>
> --
> tx12345
> ------------------------------------------------------------------------
> tx12345's Profile: http://www.excelforum.com/member.php...o&userid=24776
> View this thread: http://www.excelforum.com/showthread...hreadid=567345
>
>

[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),)


--
Arvi Laanemets
( My real mail address: arvi.laanemets<at>tarkon.ee )

[tx12345]

Thanks, the index/match formula works great.

tx