# Multiple embedded If statements in an If Function

Occasional Visitor

# Multiple embedded If statements in an If Function

I'm trying to get Excel to evaluate 3 different cells and return the one that contains a value using ISBLANK.  I intend the formula to first look in cell J3 for a value, if there is one, return that value and stop processing the statement; if there is not a value J3, look in G3, if there is one, return that value and stop processing the statement; if there is not a value in G3 look in D3 and return that value and stop processing the statement.  There will be a value in either J3, G3 or D3 without exception.  Here is the statement I entered:  =IF(ISBLANK(J15),G15,IF(ISBLANK(G15),D15,J15))

Here is an example of the spreadsheet (The first row is the column designation; the second row is the header) (the formula is in column P and is supposed to return the out time for the day):

 A B C D E F G H I J K L M N O P Q Day Date In Out Sub ttl In Out Sub ttl In Out Sub ttl In Out Dly Ttl Start Stop Total Hours Mon 01-10 8:00 16:00 08:00 00:00 00:00 08:00 08:00 00:00 -8.00 Tue 01-11 8:00 10:00 02:00 10:00 16:30 06:30 00:00 08:30 08:00 16:30 8.50 Wed 01-12 8:00 14:30 06:30 14:30 16:00 01:30 16:00 17:30 01:30 09:30 08:00 17:30 9.50

The formula works on the Wed and Tue rows, but does not work on the Mon row.  Can someone help me understand why?

Thanks,

Suzanne

2 Replies

# Re: Multiple embedded If statements in an If Function

``=IF(NOT(ISBLANK(J3)),J3,IF(NOT(ISBLANK(G3)),G3,D3))``

Is this what you are looking for?

# Re: Multiple embedded If statements in an If Function

Actually, contrary to your description, the formula IS working just fine. It's doing what it's written to do; that may not have been your intention, of course, but Excel is working just fine.

Here's how it's happening. the formula again reads like this:

=IF(ISBLANK(J15),G15,IF(ISBLANK(G15),D15,J15))

Your formula says, IF J is blank, the display whatever is in G

And in that top row column G is blank.

So the formula does exactly what it's been asked to do and displays that blank from G.

And stops.

Now, let's write a formula that does what you want it to do. Here's that description:

I intend the formula to first look in cell J3 for a value, if there is one, return that value and stop processing the statement; if there is not a value J3, look in G3, if there is one, return that value and stop processing the statement; if there is not a value in G3 look in D3 and return that value and stop processing the statement.  There will be a value in either J3, G3 or D3 without exception.

You'd really be better served with an IFS function, which does stop when it meets a condition that is satisfied.

=IFS(J3<>"",J3,AND(J3="",G3<>""),G3,AND(J3="",G3=""),D3)

By the way, you notice I didn't bother with the ISBLANK--that works, there's nothing wrong with it, but I just find it easier to designate the same by a pair of quotation marks with nothing between them. It serves the same purpose in less space.

I also think I turned your logic around: if you gave voice to your first formula, it was saying to Excel, in effect:

"Look in cell J3 and if it is blank, then get the value from G3."