0

I'm trying to use an array function in excel, the formula is to return the value of filtered table. this is my formula.

{=INDEX(A:A,MIN(IF(SUBTOTAL(3,OFFSET(A2,ROW(A2:A31)-ROW(A2),0)),ROW(A2:A31))))}

The problem is the returned value is only the first result, i.e. if after filter the first cell is A9 when i drag down the formula it will only return A9 value in all cell. This is my first try at using array function so i don't know what to check, i think the formula itself is correct but because this is my first try maybe i miss something. I already press Ctrl+Shift+Enter for validating.

Actually my goal is to return value's of cells that meets certain criteria, i tried using different function but because it's too complicated then i tried using excel built in filter but because it only hide the cells that doesn't meet the criteria, it won't work with my other formula that use the filtered value. If you have different solutions than the one i used please suggest it, but i prefer ones that not too complicated or using vba. Thanks in advance.

1

I asked the same question a while ago and get a good reply from Ron Coderre

He suggested both an array and non-array formula solution. The formulas are complex. I offer a very simple, intuitive, approach that uses simple, non-array formulas with a helper column.

Say we have data in columns A and B. In C2 enter:

=SUBTOTAL(3,$A$2:$A2)

and copy downward:

enter image description here

The helper column is a simple set of sequential values. We now filter for dog:

enter image description here

The helper has adjusted to remain a simple sequential sequence! This allows us to use a standard MATCH()/INDEX() approach to grab the visible data. In Sheet2 cell A1 enter:

=IFERROR(INDEX(Sheet1!A:A,MATCH(ROWS($1:1),Sheet1!C:C,0)),"")

and copy downward:

enter image description here

If you can't use a helper column, try one of the Coderre formulas.

  • I tried your formula and it works, it even help as reference to fix my other formula. I haven't found what make my previous formula doesn't work though, but your is simpler so i will use it for now. Thank you very much. – Dizz May 12 '18 at 17:49

Your Answer

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

Not the answer you're looking for? Browse other questions tagged or ask your own question.