IF Cell blank, go to next cell

[jalifid11]

I've got 4 cells that I'm trying to reference in a special way.

I've tried using "If" statements, but it will only go so far and then give me an error..

I've got Cell A1, B1, C1, D1..

If cell D1 is blank, return C1, if C1 is blank, return B1, if B1 is blank, return A1. Of course if D1 is not blank, return D1..

I've tried the If statement to replicate this and it does work properly.

HELP !!

 

[taigovinda]

This is a simple way to do it:

Excel Workbook
	A	B	C	D	E	F
1	1	2	3	4		4
2	1	2	3			3
3	1	2				2
4	1					1
Sheet1

Excel 2003

Cell Formulas
Range	Formula
F1	=IF(D1<>"",D1,IF(C1<>"",C1,IF(B1<>"",B1,A1)))

Paste down the formula.

 

[mikerickson]

How about

=IF(D1<>"",D1,IF(C1<>"",C1,IF(B1<>"",B1,If(A1<>"",A1,"all empty"))))

 

[taigovinda]

On a related note: DonkeyOte showed me this link: http://www.xldynamic.com/source/xld.LastValue.html#S014 wherein method #014 for finding the last value in a row looks like this:

Excel Workbook
	A	B	C	D	E	F
1	1	2	3	4
2	1	2	text	4
3
4						#N/A
5						4
Sheet1

Excel 2003

Cell Formulas
Range	Formula
F4	=INDEX(1:1,1,MAX(MATCH({"ZZZZZZZZZZZZZZZZZZZZ",9.99999999999999E+307},1:1)))
F5	=INDEX(2:2,1,MAX(MATCH({"ZZZZZZZZZZZZZZZZZZZZ",9.99999999999999E+307},2:2)))

...formula is copied down. Can anyone tell me why it returns the desired result for row 2 but not for row 1? It seems to only return anything if it finds text somewhere in the row.

Thanks in advance.

 

[mikerickson]

If the row has only numbers, MATCH("ZZZZ",1:1 returns the #N/A error, so MAX(...) is #N/A, so INDEX(1:1,MAX(...)) returns an error.

Similarly if all the entries in a row are text.

 

[taigovinda]

Thanks, Mike. That makes sense. I thought I could get around it by using lookup() instead of match(), since lookup() ignores errors. But I'm at a loss to explain this also:

Excel Workbook
	A	B	C
1	1
2		two
3	1	two
4	1	two	3
5
6	1
7	2
8	1
9	3
1

Excel 2000

#VALUE!

Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.

...this formula seems to choose the last *number* whenever there is one, giving the position of the text only when there is no number. Do you know why it does this?

Thanks,
Tai

 

[Sandeep Warrier]

Again I think its because of the 9.99999.....

The Lookup(9.9999....) is used to return the last number, and so if you find both number and text, the lookup will return the number.
<input id="gwProxy" type="hidden"><!--Session data--><input *******="jsCall();" id="jsProxy" type="hidden">

 

[mikerickson]

This CSE formula will handle all text, all number or mixed.

=INDEX(1:1,MATCH(2, IF((0 < ISNUMBER(1:1)+ISTEXT(1:1)), 1)))

It needs to be confirmed with Ctrl-Shift-Enter (Cmd+Return for Mac)

 

[satvislbdvt]

if D1,C1,B1,A1 all are formula based output,this formula is not working,pls tell

 

[satvislbdvt]

if D1,C1,B1,A1 all are formula based output,this formula(=IF(D1<>"",D1,IF(C1<>"",C1,IF(B1<>"",B1,A1)))) is not working,pls tell