sumproduct with max

[BeLZeBuT]

Hi everyone. I would appreciate your help.

I have a workbook with multiple sheets.

in the sheet1 i must return the value from the sheet by the folowing criteria>

suppose o sheet1 i select name 'John', and from the sheet2 that has the structure, i must return the addiacent cell value (col. B), that coresponding to the max value in col. C

Book1.xlsx
	A	B	C
1	John	3489655	3
2	Mary	792416,6	1
3	John	860903,6	2
4	Mary	901028,9	2
5	Mary	2923,57	3
6	John	3197,57	1
Sheet2

in this case i want to return 3489655 (cell A2).

i can only get the max for John with formula

=SUMPRODUCT(MAX(((Sheet2!A:A=A1) * (Sheet2!C:C))))

Please help me.

3489655​

 

[Special-K99]

First, 3489655 is NOT in A2 it is in B1

In your example all your data is on Sheet2

=INDEX(Sheet2!B1:B6,MATCH(MAX(Sheet2!C1:C6),Sheet2!C1:C6,0),1)

So you don't want Mary 2923,57 ? Her value in column C is also a MAX (3)

 

[BeLZeBuT]

First, 3489655 is NOT in A2 it is in B1

In your example all your data is on Sheet2

=INDEX(Sheet2!B1:B6,MATCH(MAX(Sheet2!C1:C6),Sheet2!C1:C6,0),1)

So you don't want Mary 2923,57 ? Her value in column C is also a MAX (3)

You missed in formula to get only 'John's max(3)

 

[Special-K99]

It's not clear from your description exactly what you want.

It looks like you want the value from column B that is adjacent to the maximum value in column C
The maximum number in column C is 3 (both John and Mary have this in column C).
The number adjacent to that in column B for John is 3489655
The formula returns 3489695.

 

[BeLZeBuT]

It's not clear from your description exactly what you want.

It looks like you want the value from column B that is adjacent to the maximum value in column C
The maximum number in column C is 3 (both John and Mary have this in column C).
The number adjacent to that in column B for John is 3489655
The formula returns 3489695.

Sorry for my bad english.

the formula must return the value from column B form max in column C for specific name John or Mary
In sheet1 if i change 'John' with 'Mary' the formula must return 2923,57

 

[Special-K99]

Ah, ok I think I have this now.
If you select John you want John's column B that relates to his max
But if you select Mary you want her column B that relates to her max

I have selected John or Mary in Sheet1!A1, change the formula to reflect where you select John and Mary

in Sheet1!B1
=INDEX(Sheet2!B1:B6,MATCH(MAX(Sheet2!C1:C6),IF(A1=Sheet2!A1:A6,Sheet2!C1:C6)),1)

NOTE: This is an array formula, use CTRL-SHIFT-ENTER when entering the formula, not just ENTER

 

[BeLZeBuT]

Ah, ok I think I have this now.
If you select John you want John's column B that relates to his max
But if you select Mary you want her column B that relates to her max

I have selected John or Mary in Sheet1!A1, change the formula to reflect where you select John and Mary

in Sheet1!B1
=INDEX(Sheet2!B1:B6,MATCH(MAX(Sheet2!C1:C6),IF(A1=Sheet2!A1:A6,Sheet2!C1:C6)),1)

NOTE: This is an array formula, use CTRL-SHIFT-ENTER when entering the formula, not just ENTER

For Mary result is ok, but not for John. You try this for John.

In your formula you first get the max of column C, but i'm not sure is correct. Maybe a case that the max of Mary in col. C is 4, but for John the max is 3.

 

[Special-K99]

Yep, youre right, it doesnt work for John.
I'll have another go at it....

 

[Phuoc]

Try this:

=MOD(AGGREGATE(14,6,(Sheet2!$C$1:$C$6*10^10+Sheet2!$B$1:$B$6)/(Sheet2!$A$1:$A$6="John"),1),10^10)

 

[Peter_SSs]

Another approach

BeLZeBuT.xlsm
	A	B	C
1	John	3489655	3
2	Mary	792416.6	1
3	John	860903.6	2
4	Mary	901028.9	2
5	Mary	2923.57	3
6	John	3197.57	1
Sheet2

BeLZeBuT.xlsm
	A	B
1	John	3489655
2	Mary	2923.57
Sheet1

Cell Formulas
Range	Formula
B1:B2	B1	=INDEX(Sheet2!B:B,AGGREGATE(14,6,ROW(Sheet2!B$1:B$10)/(Sheet2!A$1:A$10=A1)*(Sheet2!C$1:C$10=AGGREGATE(14,6,Sheet2!C$1:C$10/(Sheet2!A$1:A$10=A1),1)),1))