Copy data from one column or another based on a third

[2vbr]

I hope I can explain this right... but maybe not, since I've spent over an hour trying to google it without success.

I want to copy figures from two columns, based on IF a third column has the word YES in it.

Here's an example of the raw data:

1	2
1	2
1	2	YES
1	2	YES
1	2

So basically I need a formula that checks to see if the word YES is in the third column. If it's blank, then copy the data in column #1. But if it says YES, then copy the data in column #2.

So the final result would look like this:

1	2		1
1	2		1
1	2	YES	2
1	2	YES	2
1	2		1

Oh, and one more thing... but it may be too weird.

Sometimes column #2 has the digit 0 in it, which is bad. It would be nice if there was some way of saying "Copy data from column #1. If column #3 says YES, copy data from column #2... unless column #2 says 0, in which case never mind and just copy from column #1."

Like so:

1	0		1
1	0	YES	1

If that's getting too weird, that's okay; I can just sort the final results manually and copy/paste the real data over the zeros. It wouldn't take long.

But it would be wonderful if there was a formula that did the following:

Copy data from column #2 IF column #3="YES"
Copy data from column #1 IF column #3=blank, or column #2=0



Sorry again for my google-unfriendly explanation.

[AliGW]

Try this:

=IF(C2="YES",B2,A2)

By the way, did you see my improved solution here?

https://www.excelforum.com/excel-for...hing-name.html

[2vbr]

Nice!

Sorry to badger, but was there any way to account/disregard the zeroes? I just need to know wether to leave myself some time at the end to sort the results numerically and copy/paste column #1 over them or not.

(or maybe set up a second IF formula that says "If 0, copy from column #1", then a third formula that looks at both and copies any data >0. That should finally get the perfect result!)

By the way, ...]

Huh.... no, I don't know how I missed that one. But I'll definitely try to play around with it once I finish setting up this new IF formula (still getting used to Office 365. I still miss 2013).

[AliGW]

OK. This?

=IF(AND(C2="YES",B2>0),B2,IF(A2>0,A2,"")))

I still miss 2013

I don't.

[2vbr]

Whoa! NICE!

And I was just in the middle of setting up a second "If zero..." column. Happy to discard!

Thankyou VERY much!

(and 365 still feels to me like it's made for tablets/phones than a desktop PC... but I guess that's just a visual thing I'll get used to. I'm sure the under-the-hood stuff is MUCH preferred to enthusiasts)

[AliGW]

There is so much good stuff in the subscription version that it makes older versions look pretty poor in comparison (in my opinion). New functionality is being added all the time and rolled out. No going back for me!