# Issues with Excel IF(And) statement

Copper Contributor

# Issues with Excel IF(And) statement

I am attempting to use an IFS statement that when 2 cells equal the criteria, I want it to return the data from another cell.  What I've tried so far is:

=IFS(AND(B7="A", A9="Buck Openings"), X178, \$0.00), (B7="Albert", A9="Buck Openings"), X56, \$0.00), (B7="Arabella III", A9="Buck Openings"), X59, \$0.00, (B7="Ashmere", A9="Buck Openings"), Y65, \$0.00)

I get an error that it is not a formula.  Can anyone tell me what I am doing wrong - or if there is a better way to do this please?

Thanks very much for your assistance.

7 Replies

# Re: Issues with Excel IF(And) statement

=IFS(AND(B7="A", A9="Buck Openings"), "X178", AND(B7="Albert", A9="Buck Openings"), "X56",AND(B7="Arabella III", A9="Buck Openings"), "X59", AND(B7="Ashmere", A9="Buck Openings"), "Y65",TRUE, "\$0,00")

This works if you want to return texts such as "X178" or "Y65".

# Re: Issues with Excel IF(And) statement

Thank you. I am looking for it to return the contents of cell Y178 or Y65. Any other ideas? Thanks.

# Re: Issues with Excel IF(And) statement

@galley2  there are a couple issues:

=IFS(AND(B7="A", A9="Buck Openings"), X178, \$0.00), (B7="Albert", A9="Buck Openings"), X56, \$0.00), (B7="Arabella III", A9="Buck Openings"), X59, \$0.00, (B7="Ashmere", A9="Buck Openings"), Y65, \$0.00)

The first part I highlighted in red and underlined is a 'standard' IF statement:  IF( [condition], [true], [false]) and from there I get lost.

The format of an IFS() statement is IFS( [condition1], [output if condition1 true], [condition2], [output if condition2 is true], ...)

So what are all those \$0.00 ?

Maybe you mean this:

``````=IFS(AND(B7="A", A9="Buck Openings"), X178,
AND(B7="Albert",A9="Buck Openings"), X56,
AND(B7="Arabella III", A9="Buck Openings"), X59,
AND(B7="Ashmere", A9="Buck Openings"), Y65,
TRUE, 0)``````

So in the above each line is a condition and the output if it is TRUE but if not the IFS will go to the next line.  NOTICE the last line uses TRUE because you always need a condition/result pair so to create a DEFAULT value if all else if false you need this TRUE (or you can use 1 or any other value that will always evaluate to TRUE).

BTW - I put the above on multiple lines to show it more clearly but it can be all on 1 line.  To use the above in the workbook you can copy the multiple lines and just make sure you click into the formula bar or use F2 to edit the cell before you hit paste (otherwise you will see it paste into multiple cells instead of all being in 1 cell)

# Re: Issues with Excel IF(And) statement

Unfortunately i've missed the information that you want to return the data from another cell. @mtarler has meanwhile anserwed the question.

# Re: Issues with Excel IF(And) statement

Thanks so much for your response. What I am attempting to accomplish is to automatically populate cell E10 based on what is in both cell B7 and cell A9.

Example for the first statement is where
B7 equals A
and A9 equals Buck Openings
then I want it to pull the dollar amount in cell X178 into the current cell (E10), and if there is no data in X178, then I want it to return \$0.00.

I copied and pasted your formula into the formula bar and also received an error that it was not a formula.

In the past, I have successfully done an IFS statement where it was looking at only one cell and then it returned the contents of another cell, however, using 2 IF(AND) statements does not seem to be working.

# Re: Issues with Excel IF(And) statement

sorry I just notice the "AND" before each ( ) was also missing. I also put it into the code block to hopefully remove additional HTML copy paste bad characters. Try again.

EDIT: alternatively I noticed some redundancy in the formula.  I think you could use this:

``````=IF(A9="Buck Openings", SWITCH(B7,
"A", X178,
"Albert", X56,
"Arabella III", X59,
"Ashmere", Y65,
, 0),0)``````

# Re: Issues with Excel IF(And) statement

Thanks - that seems more clear, however, I keep receiving an error that there is a problem with the formula. I placed ' in front of the formula to save it, saved the document and reopened it, and if I remove ' in front of the formula, it still gives me an error that there is a problem with the formula. Any ideas why?