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

#### GSY01

##### New Member
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

<tbody>
</tbody>

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

#### Jon Peltier

##### MrExcel MVP
=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"))

#### GSY01

##### New Member
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.

#### Peter_SSs

##### MrExcel MVP, Moderator
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

Excel Workbook
BCDEFGHIJ
2StartPauseRe-setStop
32Re-set1011Re-set
43Re-set1111Re-set
52Stop1001Stop
Next Value

#### GSY01

##### New Member
Thanks Peter,

Worked perfectly thanks for your help.

Gavin

#### Peter_SSs

##### MrExcel MVP, Moderator
Thanks Peter,

Worked perfectly thanks for your help.

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