excel - Comparing column to specific value, returns the value of other column to another column -

the title might confusing here's scenario:

i want column c (new) value of column (owner) if column b (status) inactive. want print on row row, example, since column b row 3 "inactive", want owner2 print in column c row 2, , since column b row 6 "inactive" want owner5 print in column c row 3,

here formula: =iferror(index(a:a, match("inactive",b:b, 0)), "")

i have formula printed on each row in column c, doesnt intended do, because if column b row 3 inactive, formula displays owner2 value in multiple rows in column c.

i hope can answer without using excel-vb

just put in c2:

=iferror(index(a:a,small(if(b$1:b$6="inactive",row($1:$6)),row()-1)),"") 

this array formula , must conformed ctrl+shift+enter.

then copy down c2. use "evaluate formula" see how works. if there still questions left, ask.

edit

to stay non-array formula in non-array way, put in c3 (c2 stay formula):

=if(c2="","",iferror(index(index(a:a,match(c2,a:a,0)+1):a99999, match("inactive",index(b:b,match(c2,a:a,0)+1):b99999, 0)), "")) 

this non-array way, has restriction, "owner" need unique. if 1 there 2 times, may fail. if unique, formula faster bigger tables ;)