Multiple IF statements

[MrOllyR]

I'm looking for some help with an IF statements:

Formula in cell D1:

If cell A1 contains "Example 1", return ""
If cell B1 contains any data, return its value
If cell A1 contains "Example 1" and cell B1 contains no data, return the value in C1

I've tried to nest these statements but I'm not even even close. Would anybody be able to help? Thank you in advance.

 

[Fluff]

You're 1st & 3rd criteria seem to be at odds with each other.

Can you post some sample data along with expected results.

MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

 

[Joe4]

Your 1st and 3rd listings conflict with each other.
In order to come up with a good formula, we really need to know the output of each potential situation when BOTH criteria are considered.

So if we make a grid of the potential options, here is what I see.

Scenario	A1	B1	Desired Output
1​	"Example 1"	Has Data
2​	"Example 1"	No Data	return C1
3​	No Data	Has Data
4​	No Data	No Data
5​	Has Data other than "Example 1"	Has Data
6​	Has Data other than "Example 1"	No Data

Can you tell us the desired output for the other 5 options listed?

 

[MrOllyR]

Thank you for your replies. I think I've managed to work it out using this in D1 and it seems to work:

=IF(A1="Example 1","",IF(B1<>"",B1,IF(A1<>"Example 1",C1)))

 

[Fluff]

Glad you sorted it & thanks for the feedback.

 

[Joe4]

Thank you for your replies. I think I've managed to work it out using this in D1 and it seems to work:

=IF(A1="Example 1","",IF(B1<>"",B1,IF(A1<>"Example 1",C1)))

Since your have already checked to see in A1 is "Example 1", and the rest of your formula is in the FALSE clause of that check, you don't need to check it again.
So the formula you wrote can be simplified to this:

=IF(A1="Example 1","",IF(B1<>"",B1,C1))

 

[MrOllyR]

Since your have already checked to see in A1 is "Example 1", and the rest of your formula is in the FALSE clause of that check, you don't need to check it again.
So the formula you wrote can be simplified to this:

=IF(A1="Example 1","",IF(B1<>"",B1,C1))

Thank you, this is really useful!

 

[Joe4]

You are welcome.
I hope it makes sense why that extra check is not necessary (it is actually impossible to get to that point unless A1 is already not "Example1").