CODEWITHSUNDEEP
excelexcel-formula
User2341
User2341

Reputation: 45

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

enter image description here

the title might be confusing so here's the scenario:

I want the Column C (New) to get the value of Column A (Owner) if Column B (Status) is Inactive. But I want it print on row by row, for example, since Column B row 3 is "Inactive", I want to get owner2 to print in column C row 2, and also since Column B row 6 is "Inactive" I want to get owner5 to print in column C row 3,

Here is my formula: =IFERROR(INDEX(A:A, MATCH("Inactive",B:B, 0)), "")

I have this formula printed on each row in column C, but this doesnt do what I intended to do, because if Column B row 3 is Inactive, this formula displays owner2 value in multiple rows in column C.

I hope I can get answer without using Excel-VB

Upvotes: 0

Views: 83

Answers (1)

Dirk Reichel
Dirk Reichel

Reputation: 7979

Just put in C2:

=IFERROR(INDEX(A:A,SMALL(IF(B$1:B$6="Inactive",ROW($1:$6)),ROW()-1)),"")

This is an Array formula and must be conformed with ctrl+shift+enter.

Then simply copy down from C2. Just use "Evaluate Formula" to see how it works. But if there are still any questions left, just ask.

EDIT

To stay with your non-array formula in a non-array way, just put in C3 (C2 will stay with your 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 is a non-array way, but has the restriction, that ALL "Owner" need to be unique. If one is there 2 times, it may fail. But if they are all unique, then this formula will be much faster for bigger tables ;)

Upvotes: 2

Related Questions