Index/Array and return all values matching a condition

[sunriver61]

I've done this before, and can't find my sample ss... can anyone help? The Intertron is of no use on this...

On one sheet is a list of names and addresses, and I want to pull in all records that will match 'Alice' into the 'Alice' sheet.

This has been bugging me for DAYS!

Thanks if anyone can help,

[protonLeah]

Please Login or Register  to view this content.

*array entered*

[sunriver61]

Perfect! Thanks very much!

[sunriver61]

Well, it works on if there's data in the cells.... however it doesn't work if there's a formula that pulls in the data from another worksheet.... I cannot seem to win on this...

[protonLeah]

Post a more representative example file...

[Gregb11]

Does 365 ProPlus have the new dynamic array formulas? I know O365 does. If it does, you can use this in Cell A2 and it will fill in all the records:

=FILTER(Names!A2:E10,Names!A2:A10=MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,255))

This also uses the name of the sheet to get the person's name. If you don't want it to do that, here's the answer with it hard-coded:

=FILTER(Names!A2:E10,Names!A2:A10="Alice")