Hi Jacob Sharia
Thank you very much for help me and problem resolved with the following
formula

=MAX(IF((Sheet1!A2:A100=A2),Sheet1!B2:B100))
'array entered enter with CTRL+SHIFT+ENTER
thanks again

Zafar





"Jacob Skaria" wrote:

Probably will have to try with..with code in Sheet2 A2

=SUMPRODUCT(MAX((Sheet1!$A$2:$A$100=A2)*Sheet1!$B$ 2:$B$100))

OR

=MAX(IF((Sheet1!A2:A100=A2),Sheet1!B2:B100))
'array entered enter with CTRL+SHIFT+ENTER

OR without filling the blanks in sheet1 try with the below formula

=MAX(OFFSET(INDIRECT("Sheet1!A" &
MATCH(A2,Sheet1!A:A,0)),0,1,IF(ISNA(MATCH(TRUE,IND EX(INDIRECT("Sheet1!A" &
MATCH(A2,Sheet1!A:A,0)+1 &
":A1000")<"",),)),1000,MATCH(TRUE,INDEX(INDIRECT( "Sheet1!A" &
MATCH(A2,Sheet1!A:A,0)+1 & ":A1000")<"",),)),1))


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


"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