I have various styles in sheet1 with their corresponding quantities in Sheet2, I would like to Index Match to return only the highest quantity it finds not the first value it finds. Any ideas on how to incorporate it into the =INDEX(Sheet2!$A$1:$B$817,MATCH(Sheet1!A2,Sheet2!$A$1:$A$817,0),2)
Thank you,test2.xlsx
Give this formula a try. Enter with Ctrl + Shift + Enter keys enter together =MAX(IF(Sheet2!$A$2:$A$817=Sheet1!A2,Sheet2!$B$2:$B$817))
Hi there. here's one way, using an array formula.
Array Formulae are a little different from ordinary formulas in that they MUST be confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER.
You will know the array is active when you see curly braces { } appear around the outside of your formula. If you do not CTRL+SHIFT+ENTER you will (almost always) get an error message or an incorrect answer. Press F2 on that cell and try again.
Don't type the curly braces yourself - it won't work...
Glenn
None of us get paid for helping you... we do this for fun. So DON'T FORGET to say "Thank You" to all who have freely given some of their time to help YOU.
Temporary addition of accented to illustrate ongoing problem to the TT: L? fh?ile P?draig sona dhaoibh
I actually tried that since it was posted in various blogs but I get return values of 0 on all cells when entering and copying
If you're referring to JieJenn, I used the same formula in the attached sheet (Post 3). it works. Are you sure that you array-entered it?
Im sorry your right forgot the arrray enter. looks like it works!
Woo Hoo!! Glad to have helped! If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. It'd also be appreciated if you were to click the add Reputation button at the foot of any of the posts of those who helped you reach a solution.
Rep already added: thanks!!
Last edited by Glenn Kennedy; 06-29-2015 at 11:55 AM. Reason: Update..
Trying to incorporate this method to a more complex model I have. And getting no luck.
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
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks