Dear Jacob
as per your advise i did the same and result is not found for your review.

sheet 1
M.CODE Value
1002 101
1002 102
1002 103
1003 201
1003 202
1003 203
1004 301
1004 302
1004 304

Result on sheet 2

SUMPRODUCT(MAX(Sheet1!$A$1:$A$9=Sheet2!A1)*Sheet1! $B$1:$B$9)

1002 0
1003 0
1004 0

please help me

Regards



"Jacob Skaria" wrote:

In sheet1 fill the blanks with the material code
1. Select the data range in ColA (say A1:A100).
2. Press F5. From 'Goto window'Special from Options select 'Blanks' and
hit OK
3. This will select all blanks.
4. Now press = (equal sign)
5. Then press Up Arrow to reference the cell just above
6. Now press Ctrl and Enter key together

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


"vlook fomula" wrote:

Dear Jacob,
i applied same fomula but result is "0 "

"Jacob Skaria" wrote:

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