Help needed-how to use search criteria to pull cell range from large data drop

[AddSC12]

Hello!
Looking for some help on determining the best way to pull a selection of data from a large data drop.

I'am trying to filter through a large data drop- about 30000 lines or so. I have created 3 ActiveX combo boxes which are my search criteria that i would like to be used as the filters to pull the cell range on a separate sheet. So for example search criteria one is: Place, and the drop down box will give me the 17 options, then the time frame: 2 options and then the product, 5 options.

I would like those three criteria to search through about 30,000 lines to pull all the columns and rows that contain the three things.
Ive been trying to use the index and match function but i think its returning an error as im trying to pull multiple lines and have been stuck trying to find a work around. Now this could be wrong, i maybe need to use VBA or Dget function- im really not sure....so any suggestions are appreciated.

this is what i have so far

=INDEX(DataDump2!D2:K28085,MATCH(1,(DataDump2!A2:A28085=X1)*(DataDump2!C2:C28085=X2)*(DataDump2!B2:B28085=X3),0))

My X1, X2 and x3 are the cell links from my drop down lists (Place, time and product) and what i would like it to be searching by. The DataDump2 is the the column im looking to have it search through to locate however does not include the cell with the header. I have attached a simplified example, and just looking to see if there is a better function that would make this search work!

Thanks

[Pete_UK]

X2 contains Yearly (or monthly), but those are in column B of the DataDump sheet, not column C. Similarly, X3 relates to column C of that sheet.

Also, as your INDEX range covers a number of columns as well as rows, you need to have an expression which represents the column (or just make the range one column wide).

You should also be aware that this is an array formula, so it needs to be confirmed using the key combination of Ctrl-Shift-Enter (CSE), rather than the usual Enter.

So, putting those into practice on your example sheet, the formula in B3 would be:

=INDEX(DataDump2!D2:D67,MATCH(1,(DataDump2!A2:A67=X1)*(DataDump2!B2:B67=X2)*(DataDump2!C2:C67=X3),0))

You might also sile to wrap IFERROR( … ,"") around the formula, to avoid the #N/A errors which might occur (you do not have an entries for Quebec, Yearly and Dipping in the DataDump sheet).

Don't forget to use CSE to commit the formula.

Hope this helps.

Pete

[Pete_UK]

Actually, I've just re-read your post, in which you say that you want to bring across multiple lines. The formula above will not do that for you, as the MATCH function will find the first occurrence which matches the criteria.

You can do it with a different array formula (using INDEX/SMALL/ROW), but it might get a bit sluggish if you have 30,000 rows, as every element in the ranges will be examined.

Instead, I would suggest using a helper column in the DataDump sheet which identifies the records which match your criteria an allocates each a unique code. Then a simple INDEX/MATCH formula (not array-entered) can retrieve the data for you.

Let me know if you are interested in this approach (I'm going out soon).

Hope this helps.

Pete