# Thread: If formula to return a different value if a cell has 0 or is blank Thanks: 0 Likes: 0

1. ## If formula to return a different value if a cell has 0 or is blank

Hi experts,
I am new to the site, so I apologize if I have posted this in the incorrect area.

In the below table I have the following formula in C3-C5,

=IF(B3=1,\$D\$2,IF(B3=2,\$E\$2,IF(B3=3,\$F\$2,IF(B3=4,\$G\$2))))

 A1 B C D E F G 2 Start Pause Re-set Stop 3 1 Start 1 0 1 1 4 4 Stop 1 1 1 1 5 3 Re-set 1 0 0 1

As you can see if I change the number in B3-B5 the value in C3-C5 changes.

If I change B3 to 2, C3 changes to Pause.

I am trying to come up with a formula if I change B3 to 2, it can see E3 is 0 and it automatically changes to the next cell that contains a value other than 0 in this example it would be F2 (Re-set).

If I was to use B5 as the example and entered 2 in B5 it would return Stop as E5 and F5 both have 0.

I can substitute the 0 to blank cells if that makes it easier.

Thanks,
Gavin

2. ## Re: If formula to return a different value if a cell has 0 or is blank

=IF(LEN(A1)=0,"The cell has nothing in it",IF(A1=0,"The cell has zero in it","The cell has something else in it"))

3. ## Re: If formula to return a different value if a cell has 0 or is blank

Hi John,

Thanks for the reply, that didn't work how I want it.

I'm not even sure I can achieve what I want using a IF formula.

I want to be able to manually change the number in column B and the text in column C changes depending on the numerical value in D,
If D is blank it will look in E then F till a value is found, then returns the text in in headed in either E or F as a number is found.

I hope that makes sense.

4. ## Re: If formula to return a different value if a cell has 0 or is blank

Welcome to the MrExcel board!

Formula in C3 is an array formula so should be entered without the {} but confirmed with Ctrl+Shift+Enter, not just Enter. If confirmed correctly, Excel will insert the {}. The formula can then be copied down.
If you prefer not to have to use the Ctrl+Shift+Enter, I have provided an alternative normal-entry formula in column J

Next Value

 B C D E F G H I J 2 Start Pause Re-set Stop 3 2 Re-set 1 0 1 1 Re-set 4 3 Re-set 1 1 1 1 Re-set 5 2 Stop 1 0 0 1 Stop

 Cell Formula C3 {=INDEX(D\$2:G\$2,MATCH(TRUE,(D3:G3<>0)*(COLUMN(D3:G3)-COLUMN(D3)+1)>=B3,0))} J3 =INDEX(D\$2:G\$2,MATCH(TRUE,INDEX((D3:G3<>0)*(COLUMN(D3:G3)-COLUMN(D3)+1)>=B3,0),0))
Formula Array:
Produce enclosing
{ } by entering
formula with CTRL+SHIFT+ENTER!

Excel tables to the web >> Excel Jeanie HTML 4

5. ## Re: If formula to return a different value if a cell has 0 or is blank

Thanks Peter,

Worked perfectly thanks for your help.

Gavin

6. ## Re: If formula to return a different value if a cell has 0 or is blank

Originally Posted by GSY01
Thanks Peter,

Worked perfectly thanks for your help.

Gavin
You're welcome. Thanks for the follow-up.

## User Tag List

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•