Forum Discussion
If/and/or question
| consolidated | city | first | last |
| tampa | matthew | sanders |
How do I write an "if" statement for column A that returns all 3 of the words consolidated if the second two are what I am looking for?
I want column A to show, tampamatthewsanders if column B is "tampa" and column C is "matthew" and column D is "sanders" but if Column D is anything other than "sanders" I want column A to read tampamatthew only.
4 Replies
If I understand fully what you want -- you didn't specify what is to happen if B2 or C2 are something other than "tampa" or "matthew" -- this formula will work. In that non-specified situation, I wrote a formula that returns blank.
=LET(FstTwo,AND(B2="tampa",C2="matthew"),Thrd,D2="sanders",IFS(AND(FstTwo,Thrd),B2&C2&D2,FstTwo,B2&C2,NOT(FstTwo),""))- I should have been more specific, sorry. Doesn't matter what B2 and C2 are, A2 should equal B2 and C2 combined. But, if B2 is tampa, C2 is matthew, and D2 is sanders, then A2 should equal all three combined.
- How did the formulas I gave you work?
pssmatthewsanders By the way, because I used a LET function, you will need a relatively recent version of Excel for that to work.
If you don't have that new version, this will work:
=IFS(AND(B2="tampa",C2="matthew",D2="sanders"),B3&C3&D3,AND(B2="tampa",C2="matthew"),B3&C3,NOT(AND(B2="tampa",C2="matthew")),"")
