Hi, I am wondering why I got only 1 value on the top of list when I used an index (match) formula. {=INDEX(E2:E1159,MATCH(1,(A2:A1159>10)*(D2:D1159>10000),0))} How to show all the match values? Thanks~
Hi, I am wondering why I got only 1 value on the top of list when I used an index (match) formula. {=INDEX(E2:E1159,MATCH(1,(A2:A1159>10)*(D2:D1159>10000),0))} How to show all the match values? Thanks~
hh2017 if you are not aware of it that MATCH application is designed to return the first match it finds.
To return an array of index values meeting those conditions you will likely need to conditionally assign index numbers where conditions are met.
Perhaps something like this array entered in ... H2(?) and filled down until you get blanks.
Formula:Please Login or Register to view this content.
Also You might consider something like this array entered. It will return an array of matching items in E2:E1159 ... duplicating index numbers where there are duplicates in E2:E1159 and some returns of FALSE all within an array.
In all cases MATCH returns the first matching item it finds and that is all.Formula:Please Login or Register to view this content.
It all depends upon the goals you are trying to achieve.
Last edited by FlameRetired; 08-24-2017 at 12:29 AM. Reason: afterthoughts.
Dave
Thanks for the formula! No, not work. I found the filter and sort are actually very useful :-)!
Glad you found a solution.
If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks