CODEWITHSUNDEEP
excelexcel-formulaexcel-2016
noobsee
noobsee

Reputation: 962

Copy Cell value when the other cell's value are specific

I have this table:

COL_A    COL_B             COL_C
----     ------           -------
Acer      YES
ASUS      NO
Lenovo    YES
Razer     YES
VIVO      NO
...
...

I want to:

  1. Copy COL_A value to COL_C when COL_B is "NO"
  2. Whenever I added new row on COL_A with COL_B is "NO" it will automatically copied to COL_C

I tried this one:

=IF(COL_B="NO",COL_A)

But it will also count the row with COL_B = YES

COL_A    COL_B             COL_C
----     ------           -------
Acer      YES              *FALSE*
ASUS      NO               ASUS
Lenovo    YES              *FALSE*
Razer     YES              *FALSE*
VIVO      NO               VIVO

Expected result from above sample:

    COL_A    COL_B             COL_C
    ----     ------           -------
    Acer      YES              ASUS
    ASUS      NO               VIVO
    Lenovo    YES
    Razer     YES
    VIVO      NO

Upvotes: 1

Views: 41

Answers (2)

user9588528
user9588528

Reputation: 411

You can also just explictly define an empty cell where the if condition is false:

IF(COL_B="NO",COL_A,"")

example image here

Upvotes: 0

Jac
Jac

Reputation: 98

Try this (Enter the formula in C2 and Cntrl+Shift+Enter as it is an Array Formula):

=INDEX($A1:$A100,MATCH(0,IF($B1:$B100="No",COUNTIF($C$1:$C1,$A1:$A100),""),0))

Upvotes: 3

Related Questions