SUMPRODUCT, MAX, Few Criteria
 

I have imported lots of data into Excel, ive inc a small example. I need a
bit of help extracting some of the info into a table.

Col A(noderef) Col B (level) Col C (Text Format)
Node1 75 Oct1993-Oct1994
Node1 76 Oct1994-Oct1995
Node1 79 Oct1995-Oct1996
Node1 74 Oct1996-Oct1997
Node2
Node999etc

I have used the formula
SUMPRODUCT(MAX((A1:A5000=X1)*B1:B5000)) to give me the highest value of Col
B When Ref in Col A = Ref in Col X (my table).

I also want Col Y (my table) to give the show the text from Col C that
corresponds to the formula I used above. I.e. For above example

Col X = Node1, Col Y = 77.35, Col X = €œOct1995 to Oct 1996€�.

I have tried a few diff things but cant get anything to work. Sorry if Q a
bit long !
Thanks In advance
Chris

SUMPRODUCT, MAX, Few Criteria
 

"Chris26" wrote:

I have imported lots of data into Excel, ive inc a small example. I need a
bit of help extracting some of the info into a table.

Col A(noderef) Col B (level) Col C (Text Format)
Node1 75 Oct1993-Oct1994
Node1 76 Oct1994-Oct1995
Node1 79 Oct1995-Oct1996
Node1 74 Oct1996-Oct1997
Node2
Node999etc

I have used the formula
SUMPRODUCT(MAX((A1:A5000=X1)*B1:B5000)) to give me the highest value of Col
B When Ref in Col A = Ref in Col X (my table).

I also want Col Y (my table) to give the show the text from Col C that
corresponds to the formula I used above. I.e. For above example

Col X = Node1, Col Y = 79, Col X = €œOct1995 to Oct 1996€�.

I have tried a few diff things but cant get anything to work. Sorry if Q a
bit long !
Thanks In advance
Chris

SUMPRODUCT, MAX, Few Criteria
 

Sorry last post corrected mistake on my 1st post

"Chris26" wrote:

I have imported lots of data into Excel, ive inc a small example. I need a
bit of help extracting some of the info into a table.

Col A(noderef) Col B (level) Col C (Text Format)
Node1 75 Oct1993-Oct1994
Node1 76 Oct1994-Oct1995
Node1 79 Oct1995-Oct1996
Node1 74 Oct1996-Oct1997
Node2
Node999etc

I have used the formula
SUMPRODUCT(MAX((A1:A5000=X1)*B1:B5000)) to give me the highest value of Col
B When Ref in Col A = Ref in Col X (my table).

I also want Col Y (my table) to give the show the text from Col C that
corresponds to the formula I used above. I.e. For above example

Col X = Node1, Col Y = 77.35, Col X = €œOct1995 to Oct 1996€�.

I have tried a few diff things but cant get anything to work. Sorry if Q a
bit long !
Thanks In advance
Chris

SUMPRODUCT, MAX, Few Criteria
 

Try:

=INDEX(C1:C5000,MATCH(1,(A1:A5000=X1)*(B1:B5000=MA X(IF(A1:A5000=X1,B1:B5000))),0))

confirmed with CTRL+SHIFT+ENTER

or

=Lookup(2,1/((A1:A5000=X1)*(B1:B5000=Z1)),C1:C5000)


where Z1 contains the Sumproduct result from your post.


--
NBVC

Where there is a will there are many ways.
'The Code Cage' (http;//www.thecodecage.com)
------------------------------------------------------------------------
NBVC's Profile: http://www.thecodecage.com/forumz/member.php?userid=74
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=122301

SUMPRODUCT, MAX, Few Criteria
 

many thanks
will try now

"NBVC" wrote:


Try:

=INDEX(C1:C5000,MATCH(1,(A1:A5000=X1)*(B1:B5000=MA X(IF(A1:A5000=X1,B1:B5000))),0))

confirmed with CTRL+SHIFT+ENTER

or

=Lookup(2,1/((A1:A5000=X1)*(B1:B5000=Z1)),C1:C5000)


where Z1 contains the Sumproduct result from your post.


--
NBVC

Where there is a will there are many ways.
'The Code Cage' (http;//www.thecodecage.com)
------------------------------------------------------------------------
NBVC's Profile: http://www.thecodecage.com/forumz/member.php?userid=74
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=122301