Trying to find the highest match for a specific value that has multiple matches.
On Sheet Generator G2 trying to find I2 on Cutsold sheet (V2:V817) and return the style number (A2:A817) with the highest ATS Value (U2:U817)
For example:
Lets say the first Value on the list 2780 (I2) has 5 matches on the cutsold sheet. I want only the highest ATS (Available to Sell) value and tell me which Style number that is.
Thanks
Book24.xlsx
First, change your formula in V2 of CutSold to
=IFERROR(VALUE(LEFT(A2,4)),IFERROR(VALUE(RIGHT(A2,4)),IFERROR(VALUE(MID(A2,2,4)),IFERROR(VALUE(MID(A2,3,4)),0))))
and copy down.
Then, in G2 of Generator, use the array formula (enter using Ctrl-Shift-Enter)
=MAX(IF(CutSold!$V$2:$V$1000=I2,CutSold!$U$2:$U$1000))
and copy down.
Bernie Deitrick
Excel MVP 2000-2010
Thank you for the advice on V2.
Regarding my main issue the problem is I dont want the hightest ATS to display I want the Style number to display Column A on cutsold.
Basically I want this formula on G2: =INDEX(CutSold!$A$1:$V$1000,MATCH(VALUE(LEFT($K2,4)),CutSold!$V$1:$V$1000,0),1)
Except its choosing the first value of ATS it finds rather than the highest.
Combine the two: array enter
=INDEX(CutSold!$A$2:$A$1000,MATCH(MAX(IF(CutSold!$V$2:$V$1000=I2,CutSold!$U$2:$U$1000)),CutSold!$U$2:$U$1000,FALSE))
I am getting a #VALUE! Error when entering it as an array
The IFERROR formula I posted should have solved that - make sure you don't have any errors in your columns of data.
Im sorry its working now. Thanks so much!!!!!
Hey Bernie, Sorry one more question for some reason on some styles I get a return value of 1075 which is the incorrect answer but I also noticed its the first value on the cutsold sheet so cant figure out why it sends that back. Uploading attachment so you can see example on cell G5
Order Maker.xlsx
in fact any style higher than 3511 just shows 1075 as the result which is incorrect
I did not really look at the values - I expected that the Max ATS values would be unique. Since they are not, that is why you got the incorrect return. So, use this array formula in G2, and copy down:
=INDEX(CutSold!A:A,MAX(IF(CutSold!$W$1:$W$1000=K2,IF(CutSold!$V$1:$V$1000=MAX(IF(CutSold!$W$1:$W$1000=K2,CutSold!$V$1:$V$1000)),ROW(CutSold!$V$1:$V$1000)))))
Though if there are two or more entries with the same ATS value for the same code, you will get the code that is lower in the table.
Bernie, you are the man!!!
Didnt understand what you meant on the last line. But it seems to work. Its suppose to look for all possible matching root numbers and give me the corresponding style with the highest ATS
What I meant was that if your have two matching root numbers
Style Root
1234ABC 1234
1234DEF 1234
That have the same ATS
Style Root ATS
1234ABC 1234 45
1234DEF 1234 45
Then you will get 1234DEF and not 1234ABC. Not sure if that is an issue or not, just wanted you to be aware.
It is not since they have the same ATS does not matter which gets picked.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks