Thread: Lookup multiple rows of data (all data are texts)

i have sheet with many rows... and in other sheet i need to have list of all rows from data, which has one column the same...
data is like this:
name alphanumeric code town street ...
Adam sjdf30393 London Green st ...
Adam sjdf30365 Oxford Great st.
Ian asdf1201 Ascot Race st...
Dan dfasdf2010 Redding Ring st
Ian asdf1200 Ascot Bridge st

Result should have one cell where you have drop down menu with names and then under it should be list of all rows which contain name from that cell

ex. Adam

all data:
Adam sjdf30393 London Green st ...
Adam sjdf30365 Oxford Great st.

I dont know how to make it search and list all relevant rows, when there are not numeric data.
Thank you.

In Sheet1, assuming your raw data is in columns A:E, then in F2 enter:

=IF(A2=Sheet2!$A$1,COUNT($F$1:F1)+1,"")

copied down. This indicates which are matches and assumes your dropdown selection is in sheet2!A1

In Sheet2, assuming your dropdown list is in A1, then in B1:

=MAX(Sheet1!F:F)

This gives total match count,

Then in A2:

=IF(ROWS($A$1:$A1)>$B$1,"",INDEX(Sheet1!A:A,MATCH(ROWS($A$1:$A1),Sheet1!$F:$F)))

copied down as far as you want and across as many columns as you need.