Highest Value

  • Thread starter Thread starter vlook fomula
  • Start date Start date
V

vlook fomula

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
 
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
 
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
 
Hello Jacob,

There could be more than one with the max value in sheet1, I think.

I suggest to array-enter into Sheet2!C2:
=MAX((Sheet1!$A$2:$A$100=A2)*Sheet1!$B$2:$B$100))
then copy down...

Regards,
Bernd
 
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,INDEX(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
 
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
 
There is a syntax error in the formula you tried....Copy paste the formula
from my previous post...There are 3 solutions suggested in the same
post...which you can try out...

If this post helps click Yes
 
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
 
Back
Top