Search code examples
excelarray-formulas

excel: filter using formulas

I have a list of people who belong to two groups (A or B). I want to create a column which lists all the people who belong to group A. I have been trying to come up with an array formula but I do not have a working example. Any help appreciated!

Names | Group | Desired Output

Bob | A | Bob

Fred | B | Eric

Matt | B | Dave

Eric | A | Fred

Dave | A |
Stew | B |
Fred | A |

Many Thanks in advance

Solution

  • Normal formula for column C:

    C2=
=IFERROR(INDEX(A:A,AGGREGATE(15,6,ROW(B:B)/(B:B="A"),ROW()-1)), "")

    Copy/paste down in column C for a sufficient number of cells.

    Array formula: select a sufficient number of cells in column C and write the same formula in the formula bar then press Ctrl+Shift+Enter