Display multiple matches within a named range

[Nartherner]

So I am trying to populate a drop-down list with the results of a search in a named range. In this case, there will be multiple cells that satisfy the search parameter. For example, I am looking for all the dates where my salesperson "Amy" made a sale and I want those dates to be the content for a drop-down list. The table is set up such that the salesperson's name is in the left-most column and the sales dates are in a separate column so there will be multiple matches. Any formulas that will bring back multiple answers, instead of just the first one found?

Thanks

 

[alfranco17]

You need to create a named range with the OFFSET function.

Here's a tutorial: Make your data validations dynamic! [quick tip] | Chandoo.org - Learn Microsoft Excel Online

 

[Nartherner]

Thanks alfranco17. I might not be very clear in my description. This is happening within a dynamic named range. So the dynamic named range has the employee as the left-most column but there are multiple occurrences of the same employee. What I need to do is find each time the employee's name appears in the left column and return the value in a column to the right each time. Using vlookup, match, or index, stops once it finds the first occurrence. I need something that will query the entire range and return all the occurrences.

Thanks again.

 

[alfranco17]

I'd place the employee in column B and in cell A2 would enter the formula =b2 & countif(b$2:b2,b2) and fill down.
Then, on the lookup, I'd place a validation with the list of employees for the selection in A1, and then, to display the different dates starting in A3, enter this formula =iferror(vlookup(a$1&row()-2,data!a:f,column(),0),"").With that, in one column you create an index with the name plus the number of times it's been repeated, and then look it up using the name and the row number.