Thread: Array Index Match possible problem

Hi

Is it possible to write a formula that looks down column A to identify all "New" values and then capture the corresponding "Clients" in column B and finally return them in a list as per TABLE 2

TABLE 1
A B
New Client A
Client S
New Client G
Client B
New Client Y
Client T
New Client Z

TABLE 2
Client A
Client G
Client Y
Client Z

Thank you

Hello
If your data is for example in A2:B8, then the following Array formula should work.

=IFERROR(INDEX($B$2:$B$8,SMALL(IF($A$2:$A$8="New",ROW($A$2:$A$8)-1,""),ROW(A1)),1),"")

Copied down to cover however many clients might be returned. Enter with Ctrl+Shift+Enter.

Hope this helps.