Multiple IF statements and concatenation in Excel

Question

So far I've managed to concatenate cells I1 and J1 with the words "From" and "To" and put them on separate lines using:

=CONCATENATE("From: ",I1," ","To: ",J1)

I've also managed to populate column L (only if there is data in column H) using this:

=IF(H1="","","Notes: " & H1)

Now, I'd like to populate column M with these two formulas combined, but I'm lost in a maze of IF statements which don't work:

=IF(H1="","","Notes: " & H1,"",if(I1="","","From: " & I1," ",if(J1="","","From: " & J1)))

=IF(H1="","Notes: " & H1,CONCATENATE("From: ",I1," ","To: ",J1)) — iSR5, Mar 29 '16 at 04:51
Why is this downvoted? The poster has shown what they've tried, and shown a screen shot. The question is also clear... +1 — Dave, Mar 29 '16 at 07:37

score 5 · Answer 1 · answered Mar 29 '16 at 04:52

5

Concatenate() is not required to concatenate text. The & sign does the same thing and is much less typing. Consider

="From: "&I1&" "&"To: "&J1&" "&IF(H1="","","Notes: " & H1)

answered Mar 29 '16 at 04:52

teylyn

score 2 · Answer 2 · answered Mar 29 '16 at 04:48

Add the IF statement as another argument for CONCATENATE

EG:

=CONCATENATE("From: ",I1," ","To: ",J1," ", IF(H1="","","Notes: " & H1))

That said, I don't know if you really intended to reproduce the same information again. You could just do:

=K1&L1

As you've already made K1 and L1 as expected, this would just put the two together.

2 Answers2