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]

Think this does it

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

 

[BeLZeBuT]

It's stange! All the formulas that give you to me (Phuoc, Peter_SSs, Special-K99) are work with less data. But, if I try with more data (2000 rows) - I get 0, #N/A or #NUM.

 

[Peter_SSs]

I just tested all 3 formulas with 3,000 rows of data with no problems.

If you have any error values in your Sheet2 data then some of the formulas may return error values, depending on just where the error values are. Are you sure that there are no error values in Sheet2?

 

[BeLZeBuT]

Finally, the all 3 are solutions are good (offered by Phuoc, Peter_SSs, Special-K99). I've found on sheet2 a different type on column C (number and text), and i give errors. Now is good. Thank you all!

 

[Peter_SSs]

Great that you got it resolved. Thanks for letting us know. Glad we could help.