Index or Lookup function based on 2 drop down list to extract data from one sheet to anoth

You could use variants of this array formula:

=IFERROR(INDEX(Sample!$B:$B,SMALL(IF(Sample!$D$2:$D$19=IF($A$2="All",Sample!$D$2:$D$19,$A$2),IF(Sample!$G$2:$G$19=IF($A$3="All",Sample!$G$2:$G$19,$A$3),IF(Sample!$E$2:$E$19=$A$5,ROW(Sample!$D$2:$D$19)))),ROWS(A$7:A7))),"")

Array Formulae are a little different from ordinary formulae in that they MUST be confirmed in the FIRST CELL ONLY by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. After that, the array can be dragged down as normal, to cover the desired range.

You will know the array is active when you see curly brackets { } - or "curly braces" for those of you in the USA, or "flower brackets" for those of you in India - appear around the outside of your formula. If you do not use 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 brackets yourself - it won't work...

You're welcome.

---

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 all members who helped you reach a solution.

Array formulae can be slow. Especially when you are flinging a lot of work at it (the two "all") options in your dropdowns. Two suggestions.

1. Do not use excessively long ranges in the formulae. Don't set ranges to cover 10,000 rows if you only have 500.

You could also try this formula (it's non-array and a replacement for the formula originally in A7:

=IFERROR(INDEX(Sample!$B:$B,AGGREGATE(15,6,ROW(Sample!$D$2:$D$19)/((Sample!$D$2:$D$19=IF($A$2="All",Sample!$D$2:$D$19,$A$2))*(Sample!$G$2:$G$19=IF($A$3="All",Sample!$G$2:$G$19,$A$3))*(Sample!$E$2:$E$19=$A$5)),ROWS(A$7:A7))),"")

I have modified the HBP Table. Is the other one REALLY needed? Why not have another DD in A5 with the two options?

I have just checked the speed of the two formulae. They are not so very different. the non-array formula is only about 10% faster. So, maybe no great advantage in speed. So, in your real data are you using unrealistic ranges? Do you have a lot of volatile functions in the sheet (OFFSET, INDIRECT, TODAY(), etc), or array formulae and SUMPRODUCT formulae with whole column ranges?

The original formula and the modified formulae were run on each of the 14 output cells, 16,000 times, a total of about 230,000 calculations on each formula. Both formulae (array and non array) completed the task in approximately 3 seconds.

What you need are named ranges (3 of them, to be exact). CTRL-F3 to view /edit.

This is the formula for the one called Channel:

=Sample!$D$2:INDEX(Sample!$D$2:$D$10000,COUNTA(Sample!$D$2:$D$10000))

It looks down column D, as far as row 10,000 and returns the range D2:last non-blank cell. It's the best way to do what you need. The fixed ranges in the formulae are then replaced with the dynamic named ranges...

So, A7 becomes:

=IFERROR(INDEX(Sample!$B:$B,AGGREGATE(15,6,ROW(Channel)/((Channel=IF($A$2="All",Channel,$A$2))*(Service=IF($A$3="All",Service,$A$3))*(Type=$A$5)),ROWS(A$7:A7))),"")

There is no need for a named range for the $B:$B whole column reference, in this case, as it's very efficient.

As before, I've only done the elft table. Have a go at doing the one on the right yourself.