There is a solution, though not a perfect one, for this issue. You can add to the right side of the Excel table as many columns as the # of values in the dropdown list, and apply formulas to strip one value at a time into a column. Then you can filter the table by the calculated columns as you do for any other table's columns.
This would nicely solve 10-30 list items but any time you have a new item in the drop down list you will have to add a column for that item.
Here is an example using Excel 2007 though it should work for 2003 as well:
The are two tables (lists) exported: the main one and the dropdown.
The exported list had the project column with multi-item selection. Each selection has a value and next to it its ID separated by a semicolon. One of the values is 'Estimating; #1.'
In a new column named Proj-1 you can see all rows that have the value 'Estimating.' You will have to create a separate column for each separate value as explained below.
The Project column is associated with the ProjectList dropdown list: (this is an excerpt from the entire list).
For each entry in the selection list (ProjectList) you prepare a column (proj-1, proj-2 etc.) and enter the formula below:
=IF(RIGHT(Source!$B2,3)=";#1",VLOOKUP(1,ProjectList!$A$1:$D$19,2,FALSE),IF(ISERROR(SEARCH(";#1;",Source!$B2)),"",VLOOKUP(1,ProjectList!$A$1:$D$19,2,FALSE)))
The formula first tests whether that last item in the string is our item (which is ;#1 for Estimating). if not it searches for the string ;#1;. upon failure the cell will stay empty otherwise it will contain 'estimating.'
although estimating can be obtained directly from the cell or entered as a string, I found it easier to export the list and vlookup it.
Of course, for the next value you should test #2 and the lookup will be for 2. When testing for IDs greater than 9 you will need to use RIGHT(Source!$B2,4) as you need to test 4 characters.
To improve the process you can use the row() function to further automate the formula but I didn't want to add another complexity.
To improve the process you can use the row() function to further automate the formula but I didn't want to add another complexity.