EXCEL: Copy cells from column A -> C ONLY IF colum

Question

I have 2 columns of data:

COL_A  COL_B
AAAA   PM
BBBB   PM
CCCC   P
DDDD   M
EEEE   PM

I want to create a 3rd column by filtering Column A given that Column B = "PM." But I want to do so by only adding that one additional column (i.e., no stepwise/multi-column approach). The result would look like this:

COL_A  COL_B   COL_C
AAAA   PM      AAAA
BBBB   PM      BBBB
CCCC   P       EEEE
DDDD   M       
EEEE   PM      

The post, Excel formula needed: see if a text in one column appears within a text string second column, discusses (vaguely) how to do this using multiple columns along the way.

I am looking for a (non-VBA) approach to filter A by B using only 1 line of functions (i.e., only 1 additional column in the spreadsheet).

• In other words, I don't want to add column C with some function =___(A,B,...) and then column D with some function =if(C__...) based on the new column C, etc.

Can I do this?

Answer 1

In C1 enter the array formula:

=IFERROR(INDEX($A$1:$B$20,SMALL(IF($B$1:$B$20="PM",ROW($B$1:$B$20)),ROW(1:1)),1),"")

and copy down:

Array formulas must be entered with Ctrl + Shift + Enter rather than just the Enter key.

You can increase the range in the formula to match your table size.

EDIT#1:

The INDEX() function pulls data from a column based on row. THE SMALL() function creates an array of rows matching the desired criteria.