Lookup value in colA whos row matches row of index value in colB
 

Hello. I think this is a simple question, but the match, lookup, and
index functions that I've tried haven't worked. I have some experience
with XL, but I am not an expert user.

Let's say I have two columns, colA and colB. ColA is a list of time
markers; colB is a list of numerical values (measurements at each
timepoint). I want to:

1. find the max value in colB (between B52:B151).
2. find the timepoint that corresponds to the max value computed in 1.

The result that I want is the value computed in 2.
Any assistance would be greatly appreciated.

Gwen


--
Gwen Frishkoff
------------------------------------------------------------------------
Gwen Frishkoff's Profile: http://www.excelforum.com/member.php...o&userid=15907
View this thread: http://www.excelforum.com/showthread...hreadid=273889

=INDEX(A52:A151,MATCH(MAX(B52:B151),B52:B151,0))

Hope this helps!


--
Domenic
------------------------------------------------------------------------
Domenic's Profile: http://www.excelforum.com/member.php...o&userid=10785
View this thread: http://www.excelforum.com/showthread...hreadid=273889

Try this in a cell formatted the same as your ColumnA:

=INDEX(A52:A151,MATCH(MAX(B52:B151),B52:B151,0))
--

HTH,

RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================


"Gwen Frishkoff" wrote in
message ...

Hello. I think this is a simple question, but the match, lookup, and
index functions that I've tried haven't worked. I have some experience
with XL, but I am not an expert user.

Let's say I have two columns, colA and colB. ColA is a list of time
markers; colB is a list of numerical values (measurements at each
timepoint). I want to:

1. find the max value in colB (between B52:B151).
2. find the timepoint that corresponds to the max value computed in 1.

The result that I want is the value computed in 2.
Any assistance would be greatly appreciated.

Gwen


--
Gwen Frishkoff
------------------------------------------------------------------------
Gwen Frishkoff's Profile:
http://www.excelforum.com/member.php...o&userid=15907
View this thread: http://www.excelforum.com/showthread...hreadid=273889

What you want is a Top 1 (Max) list of time points, based on
measurements. What follows is a formula system that takes the ties of
the max measurement value into account...

Lets consider the following sample (smaller than yours for illustrative
purposes).

Let A1:B59 house the following sample:

{"Time","Value";
"time-1",100;
"time-2",125;
"time-3",110;
"time-4",140;
"time-5",140;
"time-6",120;
"time-7",110;
"time-8",110}

In C51 enter: Rank.

In C52 enter & copy down:

=RANK(B52,$B$52:$B$59)+COUNTIF($B$52:B52,B52)-1

In E48 enter:

=MAX(B52:B59)

In E49 enter: 1 (meaning Top N = 1)

In E50 enter:

=COUNTIF(B52:B59,LARGE(B52:B59,E49))-1

In E51 enter: Top List Time Points

In E52 enter & copy down:

=IF(ROW()-ROW(E$52)+1<=$E$49+$E$50,INDEX($A$52:$A$59,MATCH(R OW()-ROW(E$52)+1,$C$52:$C$59,0)),"")

The ROW(E$52) anchors the formula to the first cell the formula is
entered, which is E52.

The results list will show:

{"time-4";"time-5"}

given the sample under consideration.

Gwen Frishkoff Wrote:
Hello. I think this is a simple question, but the match, lookup, and
index functions that I've tried haven't worked. I have some experience
with XL, but I am not an expert user.

Let's say I have two columns, colA and colB. ColA is a list of time
markers; colB is a list of numerical values (measurements at each
timepoint). I want to:

1. find the max value in colB (between B52:B151).
2. find the timepoint that corresponds to the max value computed in 1.

The result that I want is the value computed in 2.
Any assistance would be greatly appreciated.

Gwen


--
Aladin Akyurek
------------------------------------------------------------------------
Aladin Akyurek's Profile: http://www.excelforum.com/member.php...fo&userid=4165
View this thread: http://www.excelforum.com/showthread...hreadid=273889