# Filter list data using only formula

1. ## Filter list data using only formula

Hi there,

I need to Filter a range of data based on the respective values in 2 different forms.  Register To Reply

2. ## Re: Filter list data using only formula

Hi. Part 1, Design Number (array formula):
Formula:  `Please Login or Register  to view this content.`

Order Quantity:
IFERROR(VLOOKUP(A16,\$A\$4:\$B\$9,2,FALSE),"")

Part 2, Design Number (array formula):
=IFERROR(INDEX(\$A\$4:\$A\$9,SMALL(IF(\$B\$4:\$B\$9=B26,ROW(\$B\$4:\$B\$9)-3,""),1+COUNTIF(\$B\$25:B25,B26))),"")

Order quantity (array formula):
=IFERROR(LARGE(IF(\$B\$4:\$B\$9>0,\$B\$4:\$B\$9,""),ROWS(\$B\$26:B26)),"")

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.

Happy to explain, if needed...

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...  Register To Reply

3. ## Re: Filter list data using only formula

Wow.. Thanks. it works well. You are a great guru and magician..   Register To Reply

4. ## Re: Filter list data using only formula  Register To Reply