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

[GSY01]

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))))

[TABLE="width: 448"]
<tbody>[TR]
[TD]A1
[/TD]
[TD]B
[/TD]
[TD]C
[/TD]
[TD]D
[/TD]
[TD]E
[/TD]
[TD]F
[/TD]
[TD]G
[/TD]
[/TR]
[TR]
[TD]2
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]Start
[/TD]
[TD]Pause
[/TD]
[TD]Re-set
[/TD]
[TD]Stop
[/TD]
[/TR]
[TR]
[TD]3
[/TD]
[TD]1
[/TD]
[TD]Start
[/TD]
[TD]1
[/TD]
[TD]0
[/TD]
[TD]1
[/TD]
[TD]1
[/TD]
[/TR]
[TR]
[TD]4
[/TD]
[TD]4
[/TD]
[TD]Stop
[/TD]
[TD]1
[/TD]
[TD]1
[/TD]
[TD]1
[/TD]
[TD]1
[/TD]
[/TR]
[TR]
[TD]5
[/TD]
[TD]3
[/TD]
[TD]Re-set
[/TD]
[TD]1
[/TD]
[TD]0
[/TD]
[TD]0
[/TD]
[TD]1
[/TD]
[/TR]
</tbody>[/TABLE]


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]

=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]

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]

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
	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
Next Value

 

[GSY01]

Thanks Peter,

Worked perfectly thanks for your help.

Gavin

 

[Peter_SSs]

Thanks Peter,

Worked perfectly thanks for your help.

Gavin

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