Excel 2010 VLOOKUP to match two columns and retrieve the MAX value from the third column

Question

I have an excel file that contains two worksheets. Sheet1 contains column A that I need to match to another column A in Sheet2. Where there is a match between Sheet1 Column A and Sheet2 Column A, I want the MAX value from the third column B from Sheet2. Sheet2 Column A has duplicate values and I want the MAX value from Sheet2 Column B to be copied to Sheet1 Column B.

I would appreciate any help on this.

Thanks.

Example below:

Sheet1

Column A Column B
A1234         
A2345
A3456
A4567

Sheet2

Column A Column B
A1234    20020105   
A1234    20020105
A2345    20010305
A3456    20100505
A3456    20100605

I have tried the below formula and can't quite get it to work

=match(Sheet1!A2,Sheet2!A2:A26283,0),vlookup(A2,Sheet2!A2:B26283,2,0), "") 

Added Info: Sheet2 can be sorted.

How I resolved my issue:

I created a custom sort in sheet2, first by Column A asc, then added another level to sort Column B in desc order. That allowed for the MAX column being on top for where Column A values were duplicated.

I then copied and paste the two columns from sheet2 into sheet 1. I now did a VLOOKUP where sheet1 Column A matched my most left column from the lookup table which has been locked. The VLOOKUP returned the first value where a match existed for index = 2 which is the 2nd column in the lookup table. The values now exist in the Sheet1 Column B field.

Thanks for pointing me in the right direction.

Answer 1

Use this formula in B1 on Sheet 1:

=AGGREGATE(14,6,Sheet2!$B$1:$B$5/(Sheet2!$A$1:$A$5=A1),1)

Or if you have Office 365 Excel use this:

=MAXIFS(Sheet2!B:B,Sheet2!A:A,A1)

And copy down.