Problem using the IF function and Indirect together.

[Blue Baron]

I am having a problem combining the IF function together with the Index function. This first two formulas listed below work, but when I take the two ideas and put them together to create the third function, I get a #Value error. Can anyone explain why? I am using Control+Shift+Enter on all of them.

=MATCH("B",INDIRECT(ADDRESS(33,COLUMN(AA33)-3,4)&":"&ADDRESS(33,COLUMN(AA33),4)),0)
=IF(X33:AA33="",0,X33:AA33)
=IF(INDIRECT(ADDRESS(33,COLUMN(AA33)-3,4)&":"&ADDRESS(33,COLUMN(AA33),4))="",0,99)

 

[Jonmo1]

The 2nd and 3rd formulas you posted DO work the same way (except the 3rd one returns 99 if the if is false).
But they're probably not doing what you think they do.

=IF(X33:AA33="",0,X33:AA33)

X33:AA33 isn't a valid reference for this function.
The IF is expecting to evaluate a single value.
But since you provided a multicell range for IF to evaluate, Excel basically tries to choose which single cell you meant to use.
And it makes that choice based on where you entered the formula.
If the formula is entered in Column X, then it uses X33
=IF(X33="",0,X33)
if it's entered in column Z, then it uses Z33.
=IF(Z33="",0,Z33)

If the formula is entered in any column outside of X to AA, then you get the #Value ! error because it has no logic to follow when choosing which cell to use.

The formula you constructed with Indirect, works exactly the same (except returning 99 when false).
I would imagine that you get the #Value ! error because you put the formula outside of columns X - AA.

 

[Blue Baron]

The 2nd and 3rd formulas you posted DO work the same way (except the 3rd one returns 99 if the if is false).
But they're probably not doing what you think they do.

=IF(X33:AA33="",0,X33:AA33)

X33:AA33 isn't a valid reference for this function.
The IF is expecting to evaluate a single value.
But since you provided a multicell range for IF to evaluate, Excel basically tries to choose which single cell you meant to use.
And it makes that choice based on where you entered the formula.
If the formula is entered in Column X, then it uses X33
=IF(X33="",0,X33)
if it's entered in column Z, then it uses Z33.
=IF(Z33="",0,Z33)

If the formula is entered in any column outside of X to AA, then you get the #Value ! error because it has no logic to follow when choosing which cell to use.

The formula you constructed with Indirect, works exactly the same (except returning 99 when false).
I would imagine that you get the #Value ! error because you put the formula outside of columns X - AA.

Thank you for taking a look at it, but I have used the IF function successfully with ranges many times before, (and it is working correctly in the second formula) usually referring to a range on a different worksheet. In this case all the formulas shown are in Column W.

 

[Blue Baron]

The problem may lie with the Address function. Following formula does not work either, and it is referring to only one cell. It returns FALSE when in fact Cell AA33 does equal "d".

=IF(ADDRESS(ROW(AA33),COLUMN(AA33),4)="d",80,99)

 

[Jonmo1]

You don't have indirect there.
=IF(INDIRECT(ADDRESS(ROW(AA33),COLUMN(AA33),4))="d",80,99)

The fact that it worked before is concidental, and we're using the word 'worked' very loosely. i.e. 'worked' basically means it returned a value and not an error.
It may have returned desired results, but it was not doing it in the way you thought it was, and it was coincidental that it returned desired results.
In fact, are you sure the results it returned in the past were actually 'the correct' results ??

 

[Blue Baron]

Yes, they did return the correct results.

 

[Jonmo1]

It's coincidental that it returned correct results (basically had the formula placed in a cell that was in the same row or column as the range it references)

Here's an example
Your formula in Row W34:AB34 (W34 and AB34 return #Value ! error because it is not in a row or column relative to the range referenced)
The proper way to write the formula with a single cell reference is in W35:AB35

Book1
	W	X	Y	Z	AA	AB
33	a	b	c	d	e	f
34	#VALUE!	b	c	d	e	#VALUE!
35	a	b	c	d	e	f
Sheet1

Cell Formulas
Range	Formula
W34	=IF(X33:AA33="",0,X33:AA33)
W35	=IF(W33="",0,W33)
X34	=IF(X33:AA33="",0,X33:AA33)
X35	=IF(X33="",0,X33)
Y34	=IF(X33:AA33="",0,X33:AA33)
Y35	=IF(Y33="",0,Y33)
Z34	=IF(X33:AA33="",0,X33:AA33)
Z35	=IF(Z33="",0,Z33)
AA34	=IF(X33:AA33="",0,X33:AA33)
AA35	=IF(AA33="",0,AA33)
AB34	=IF(X33:AA33="",0,X33:AA33)
AB35	=IF(AB33="",0,AB33)

 

[Jonmo1]

You can see that the formula in X34 is really only looking at the value of X33 (Because it's in the same column)
Same for Y34 - refers to Y33, Z34 refers to Z33 and AA34 refers to AA33.

 

[Blue Baron]

You can see that the formula in X34 is really only looking at the value of X33 (Because it's in the same column)
Same for Y34 - refers to Y33, Z34 refers to Z33 and AA34 refers to AA33.

Are you using Control+Shift+Enter (Command+Shift+Enter on Mac) when you initialize the formula? If you don't you will get #Value returned. I can put this formula ,=IF(X33:AA33="",0,X33:AA33), anywhere on the worksheet and it will work, but I have to use Control+Shift+Enter when I enter it to make it work.

 

[Jonmo1]

Are you highlighting a 4 cell range, say A1:D1, typing that formula and pressing CTRL + SHIFT + ENTER ?
That makes sense now..


It's the COLUMN function (as well as ROW) that can't return as an Array
Standby