In Sheet2 with material code in cell A1
=SUMPRODUCT(MAX((Sheet1!$A$1:$A$100=A1)*Sheet1!$B$ 1:$B$100))

If this post helps click Yes
---------------
Jacob Skaria


"vlook fomula" wrote:

Hi,
If sheet1 has some data which is each material code has diff supplier rate
now I want highest rate on sheet 2 against material code how I can do that
with the formula?
Example
sheet1
M.code S.rate
1002 105
104
106

1003 110
102

1004 125
130

sheet2
M.code S.rate
1002 106
1003 110
1004 130

Regards
Zafar