Honestly, using complicated array formulas to fetch results with that much data would just slow the workbook down to probable non-usability. I would have to stick with Alan's original suggestion of simply using the auto filter, which can do what you've described already. If there is some special need to not use the built in Filter functionality, then I would have to recommend using VBA to fetch the results. It can work with that much data easily and output the results as values in order to keep the sheet working quickly and smoothly. But my first recommendation matches Alan's: Filters can (and should) handle this task.