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]

This does NOT work
=IF(INDIRECT(ADDRESS(33,COLUMN(AA33)-3,4)&":"&ADDRESS(33,COLUMN(AA33),4))="",0,99)
But this DOES
=IF(INDIRECT(ADDRESS(33,27-3,4)&":"&ADDRESS(33,27,4))="",0,99)

Try the columnS function
=IF(INDIRECT(ADDRESS(33,COLUMNS(A1:AA33)-3,4)&":"&ADDRESS(33,COLUMNS(A1:AA33),4))="",0,99)

 

[Blue Baron]

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

I am not highlighting anything, just typing it it, which gives the same result, but yes, I am using CTRL+SHIFT+ENTER.

This formula works just fine, but stops working if I put an IF function in it.

{=MATCH("b",INDIRECT(ADDRESS(ROW(AA$33),COLUMN(AA$33)-3,4)&":"&ADDRESS(ROW(AA$33),COLUMN(AA$33),4)),0)}

The above formula works the one below does not. it returns #Value

{=IF(INDIRECT(ADDRESS(ROW(AA$33),COLUMN(AA$33)-3,4)&":"&ADDRESS(ROW(AA$33),COLUMN(AA$33),4))="","Yes","No")}

 

[Jonmo1]

ok, so you're entering it into a single cell..
Let's forget about the indirect, and take the basic formula
=IF(X33:AA33="",0,X33:AA33)

What exactly are you expecting that formula to do ?
Why are you not just writing =IF(X33="",0,X33)
Because that's what the formula ends up actually doing.

FYI, the MATCH works fine with indirect because the 2nd argument of Match expects a Range or an Array. While the IF expects to evaluate only a single value.

 

[Blue Baron]

ok, so you're entering it into a single cell..
Let's forget about the indirect, and take the basic formula
=IF(X33:AA33="",0,X33:AA33)

What exactly are you expecting that formula to do ?
Why are you not just writing =IF(X33="",0,X33)
Because that's what the formula ends up actually doing.

FYI, the MATCH works fine with indirect because the 2nd argument of Match expects a Range or an Array. While the IF expects to evaluate only a single value.

Ultimately, what I want to do is find blanks within an array. Unfortunately, Match will not find blanks, so I need to get something else to work with Match to find the blanks. Since I will be looking at multiple arrays, one at a time, I did not want to hard code the array into the formula. I want to copy the formula to multiple locations with minimal reworking.

 

[Jonmo1]

So then this
=IF(X33:AA33="",0,X33:AA33)
You're tyring to say, if ANY cell within X33:AA33 is Blank, then ?? (do what?, return 0?) ?, If NONE are blank then ??? do what?
Have you tried countblank ?
=IF(COUNTBLANK(X33:AA33)>0,0,???) Still not clear what should happen if none are blank..

 

[Jonmo1]

Try this to Match against a blank cell

=MATCH(TRUE,(X33:AA33=""),0)
Entered with CTRL + SHIFT + ENTER

Or like this without CSE
=MATCH(TRUE,INDEX(X33:AA33="",0),0)

 

[Blue Baron]

If it is blank, then return the value that comes before it in the array. In other words, the last non-blank value.

 

[Jonmo1]

You want to find the last non blank in X33:AA33 ?

Are those values TEXT or NUMBER, or could be either ? (We're getting there, there are great formulas for this task)

 

[Blue Baron]

Text

 

[Jonmo1]

If they're NUMBERS
=LOOKUP(9.99999999999999E+307,X33:AA33)

If They're TEXT
=LOOKUP(REPT("Z",255),X33:AA33)

These give the actual value of the last non blanks...
If you want the index # like match returns, then

If they're NUMBERS
=MATCH(9.99999999999999E+307,X33:AA33,1)

If They're TEXT
=MATCH(REPT("Z",255),X33:AA33,1)




If the values could be either number or text, then this for the index #
=MATCH(2,1/(X33:AA33=""),1)

For the actual value
LOOKUP(2,1/(X33:AA33=""),X33:AA33)