# Help to create list based on multiple criteria (example attached)

1. ## Help to create list based on multiple criteria (example attached)

Hi All,

I am trying to formulate a commission form which looks up the name of the sales person and then displays a list of the deal names withing Q1. I have attached a mock up so you get the idea of what i want.

Any help would be much appreciated.

Test List.xlsx

2. ## Re: Help to create list based on multiple criteria (example attached)

Try this ARRAY formula, in H6, copied down...
=IFERROR(INDEX(\$B\$1:\$B\$12,SMALL(IF(\$D\$1:\$D\$12=\$I\$3,IF(\$A\$1:\$A\$12>=\$M\$2,IF(\$A\$1:\$A\$12<=\$M\$3,ROW(\$A\$1:\$A\$12)))),ROWS(\$A\$1:A1))),"")

...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 your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer. Press F2 on that cell and try again.

There are currently 1 users browsing this thread. (0 members and 1 guests)