Excel possible to fill two cells with one if-formula

Question

I have searched on the internet to look if this is possible :

IF(logical test ; true value ; false value )

This is what i try to make for example

A2 =IF( B2 = 20 ; "True" & give C2 value "Closed" ; False )

How can I do this?

Solution 1

No, you can't assign a value to another cell without VBA.

You could do two if formulas like:

A2=IF(B2=20,"True","False")
C2=IF(A2="True","Closed","")

For VBA you could do something like

sub test()
  For each c in range("B:B")
    If c = 20 then
      c.offset(-1,) = "True"
      c.offset(1,) = "Closed"
    End if
  Next c
end sub

Solution 2

For future reference, and maybe for others searching for an answer in future, you can do it with a single formula if the cells are adjacent. E.g. if we were putting TRUE/FALSE in C2 and Closed in D2.
You do this with array formula... To do this you need to:

1. Select BOTH cells C2 & D2
2. Enter the formula =IF($B2=20,{TRUE,"Closed"},{FALSE,""}) and use Ctrl+Shift+Enter to set it

In this way, the IF is returning an array of values (either TRUE,"Closed" or FALSE,"") and, by entering this as an array formula with the two cells selected, Excel will populate both.

Unfortunately, you can't do this approach when the cells are split, like in A2/C2 as in your case :-(

Comments

[Tetsujin]

It's not really clear what you're after. If that function was in C2, then =IF(B2 = 20,"True","False") then in C3 =IF(C2 = "True","Closed") Would that not do what you wanted? As far as I'm aware you cannot 'push' a value to another cell, only 'pull' one

[Beer]

Ooh if you can't Push a value to another cell then you already awnserd my question What i wanted to do is if i put this IF statement in say cell A2 and B2 had value 20 Then i want A2 get value True and C2 get value Closed.

[Tetsujin]

Then it would need 2 IF calculations, one checking for B2=20 & the other either for the same thing, or for A2="True".

[Tetsujin]

BTW, did you notice you don't need a 'false' value, only the 'true' is compulsory.

[Beer]

Thanks @Tetsujin for the awnser, but what i would like to try is to achieve a function in one column. I know your way works aswell! And yes i know i don't need a False aswell hehe

[Beer]

Thanks i will try your VBA soon, i never used VBA before but have a programming background :)

[Raystafarian]

@Beer something I didn't put in is, of course, you'll want to limit your for loop to probably a defined range or .usedrange

[Beer]

Well if you really want to help me then i need to give you a lot more information then this easy example, Like i work with 3 different sheets "Old" , "New" "Combine" What i try to do first is check Old and New ID's on duplicate and new/old entries for now i use this formule =IF(ISERROR(VLOOKUP(New!C9;Old!$B$3:$B$998;1;FALSE));IF(New!‌​C9=0;"Empty";"NEW");‌​"Duplicate") so i cant check for myself if it works, and for example if there is a New entrie get this row and paste it in the "Combine" Sheet

[Raystafarian]

If you want to use a macro, all of those things are relatively easy to implement. There are a lot of macro examples out there for checking duplicates and for combining rows/columns. If you are headed that way, give it a shot and then post another question showing your code and where you're getting stuck.

[Beer]

I will give it a Shot, The problem is that i try to make it easy for other people at my work to use it

[CallumDA]

For something simple like this, if you are trying to make it easy for your work colleagues, avoid VBA.

[Raystafarian]

@Beer if you're worried coworkers won't know how to use it, just tie it to a button press for a button on the sheet.