Forum Discussion

Copper Contributor

Feb 14, 2019

How to return multiple results based on multiple criteria

I'd like to return data from multiple worksheets based upon multiple criteria. Here is an example: Data Table 1 (in worksheet 1) Item Location Qty this east 5 that west 10 anothe...

excel

Formulas and Functions

Twifoo

Silver Contributor

Feb 15, 2019

Hello Andrew,

Sheet3 of the attached file returns the filtered data, as you specified. Note that the formulas in Sheet3 must be accordingly adjusted as more data tables are added, thereby resulting in an extremely long formula that may cause Excel to crash!

Nonetheless, I suggest that instead of adding more data tables, data should be combined into only one table, as shown in Sheet4, so that filtered data can be returned, with automatically sorted items, as shown in Sheet5.

I eschewed volatile functions in the formulas therein.

Cheers!

Twifoo

MultipleCriteria.xlsx15 KB

Andrew Lindsay

Copper Contributor

Feb 18, 2019

Twifoo, thank you! My apologies for not responding sooner. I was out on PTO the last half of last week. Your solution will do exactly as we need. I've taken a little time this morning to examine how you accomplished the task. It's quite elegant. But as you mentioned, by the time we add in several criteria and 20-ish locations then Excel may have an issue either with the formula or in performance. I haven't tested that yet. But I will and I'll get back to you on how well it works.

Thanks again,
Andrew