Find max corresponding value of multiple instances

[Eddy2]

Hi
Is there a formula to solve this problem in excel:
Sheet 1 – write a number in col. A à find all instances of that number in sheet 2, col. A, and the corresponding values in sheet 2 col. B. à Return the highs corresponding value in sheet 1 col B.

I.e. If I write 100 in cell A3 (sheet 1) the formula returns 30 in cell B3 ((sheet 1).

 

[63falcondude]

Check out the MAX IF array function.

=MAX(IF(Sheet2!$A$2:$A$10=A2,Sheet2!$B$2:$B$10)) Ctrl Shift Enter

 

[Eddy2]

Check out the MAX IF array function.

=MAX(IF(Sheet2!$A$2:$A$10=A2,Sheet2!$B$2:$B$10)) Ctrl Shift Enter

This works. Cheers!

Also got another solution that works:

IF you have Office 365 Excel use this:
<code style="margin: 0px; padding: 0px; border: 0px; font-family: Consolas, Menlo, Monaco, "Lucida Console", "Liberation Mono", "DejaVu Sans Mono", "Bitstream Vera Sans Mono", "Courier New", monospace, sans-serif; white-space: inherit;">=MAXIFS(Sheet2!B:B,Sheet2!A:A,A3)
</code>If you do not have Office 365 but later then 2010:
<code style="margin: 0px; padding: 0px; border: 0px; font-family: Consolas, Menlo, Monaco, "Lucida Console", "Liberation Mono", "DejaVu Sans Mono", "Bitstream Vera Sans Mono", "Courier New", monospace, sans-serif; white-space: inherit;">=AGGREGATE(14,6,Sheet2!B1:B100/(Sheet2!A1:A100=A3),1)
</code>If you are using 2007 or later then you will need to use this array formula:
<code style="margin: 0px; padding: 0px; border: 0px; font-family: Consolas, Menlo, Monaco, "Lucida Console", "Liberation Mono", "DejaVu Sans Mono", "Bitstream Vera Sans Mono", "Courier New", monospace, sans-serif; white-space: inherit;">=MAX(IF(Sheet2!A1:A100=A3,Sheet2!B1:B100))
</code>Being an array formula it needs to be confirmed with Ctrl-Shift- Enter instead of Enter when exiting edit mode. If done correctly then Excel will put <code style="margin: 0px; padding: 1px 5px; border: 0px; font-size: 13px; font-family: Consolas, Menlo, Monaco, "Lucida Console", "Liberation Mono", "DejaVu Sans Mono", "Bitstream Vera Sans Mono", "Courier New", monospace, sans-serif; background-color: rgb(239, 240, 241); white-space: pre-wrap;">{}</code> around the formula.

 

[63falcondude]

Great! Glad to help. I am working with Excel 2010 so unfortunately MAXIF and MAXIFS are unavailable for me.