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

[Gwen Frishkoff]

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

Let's say I have two columns, colA and colB. ColA is a list of tim
markers; colB is a list of numerical values (measurements at eac
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.

Gwe

 

[Domenic]

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

Hope this helps!

 

[RagDyeR]

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!
==============================================


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

 

[Aladin Akyurek]

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

Lets consider the following sample (smaller than yours for illustrativ
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(ROW()-ROW(E$52)+1,$C$52:$C$59,0)),"")

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

The results list will show:

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

given the sample under consideration.