SUMPRODUCT, MAX, Few Criteria

[Chris26]

I have imported lots of data into Excel, i’ve 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 can’t get anything to work. Sorry if Q a
bit long !
Thanks In advance
Chris

 

[Chris26]

I have imported lots of data into Excel, i’ve 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 can’t get anything to work. Sorry if Q a
bit long !
Thanks In advance
Chris

 

[Chris26]

Sorry last post corrected mistake on my 1st post

 

[NBVC]

Try:

=INDEX(C1:C5000,MATCH(1,(A1:A5000=X1)*(B1:B5000=MAX(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

 

[Chris26]

many thanks
will try now