Incorporating SMALL into a INDEX and MATCH Formula

Hi there,

I'm trying to create dynamic drop-down lists and would maybe? like to use the 'SMALL' formula to return multiple results (in the event there are multiple items in a list that could return different results).

Currently I am using =INDEX(INDIRECT(H4),MATCH(U4,INDIRECT(G4),0)). I have created tables and named ranges that the indirect formula refers to. However, I have instances where the same name is used more than once, but the matched adjacent cell may have a different Value. I am using this formula as a data validation tool with a drop down as a 'list', I would like all values (May, August, and September to populate in the drop down if 'HOME' was selected as the primary reference. (see below table for an example).

Example:
Table 1:

Location Month

HOME MAY
WORK JUNE
MALL JULY
HOME AUGUST
HOME SEPTEMBER

Please help and let me know if further clarification would be helpful.

Try this aproach

Your data in B3:C7

H4 have the filter HOME

use the following formula and copy down to get the list

Formula:

Please Login or Register  to view this content.

See the file

Thank you for the reply. This is a very interesting formula, I will definitely use it in the future. Thank you!

Unfortunately, I am looking for a formula that will essentially do exactly what you've done here but in a list format. I don't have the space to populate multiple cells and would need something that could auto-populate a drop down list dynamically, the user would then pick the appropriate matched value (May, August, or September) in this example.

Thank you,
Travis.