Return multiple items from two and three lookup values

For this particular problem, the raw data sits in column A through F (A2 through F20). Column G though M in TAN color is driven by formulas to extract distinct values from the raw data table. Three columns (column I, K and M) are utilized in creating Named Ranges for setting up Data Validation on the ExtraValues worksheet and ExtraValues (2) worksheet.

With this set up, I believe there is an Excel formula to extract multiple values from the raw data table in the Sample Data worksheet when a value is selected from the dropdown list for UniqueDay and UniqueDate. For example, if Wednesday and 2/1/2012 were selected from the dropdown lists in the ExtraValues worksheet, the Excel formula should return the desired values (in GREEN COLOR). See ExtractValues worksheet for desired outcome.

I also need an Excel formula to extract multiple values in the ExtractValues (2), except that we have three lookup values in this situation.

UniqueDay UniqueDate Rep Count
Wed 2/1/2012 Stone 7

Please Login or Register  to view this content.

See attached sample data for your convenience.

Thanks for your help in advance.

hi bjnockle, try this array formula in ExtractValues A6:

Formula:

Please Login or Register  to view this content.

...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you might get an error or a clearly incorrect answer. Press F2 on that cell and try again.
copy down

B6 and the rest can simply be:
=VLOOKUP($A6,'Sample Data'!$A$2:$F$20,COLUMNS($A6:B6),0)

in ExtractValues (2), simply add this extra portion in red:

Formula:

Please Login or Register  to view this content.

VLOOKUP is the same

you're welcome. try this for the 2nd formula then:

Formula:

Please Login or Register  to view this content.

if you have other new scenarios, do start a new thread instead. If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.

forgot to change the lookup_value to fit your worksheet:

Formula:

Please Login or Register  to view this content.