# Issue with multiple conditions and column()

#### lovallee

##### Board Regular
Hello,

I am using the following array formula (confirmed with CTRL+SHIFT+ENTER), the result of which will be used in another OFFSET() formula. The formula is intented to return a column number based on two conditions.

However, the formula always return 0.

=IF(\$C\$1:\$AV\$10="Balance",IF(\$C\$1:\$AV\$10="USD",COLUMN(\$A\$1:\$AV\$10),),)

Any ideas why it fails?

Thank you.

Louis

##### MrExcel MVP
Hello,

I am using the following array formula (confirmed with CTRL+SHIFT+ENTER), the result of which will be used in another OFFSET() formula. The formula is intented to return a column number based on two conditions.

However, the formula always return 0.

=IF(\$C\$1:\$AV\$10="Balance",IF(\$C\$1:\$AV\$10="USD",COLUMN(\$A\$1:\$AV\$10),),)

Any ideas why it fails?

Thank you.

Louis
Suppose that:

C2 = Balannce

and

F2 = USD

What result are you expecting to return?

#### lovallee

##### Board Regular
Oupss I made a small error copying the array formula:
=IF(\$C\$10:\$AV\$10="Balance",IF(\$C\$11:\$AV\$11="USD",COLUMN(\$A\$1:\$AV\$1),),)

Suppose that:
C10 = Balance
D10 = Balance
E10 = Movement
and
D11 = USD
E11 = USD

I would expect the fomula to return 4, which is the first column number that meets both conditions. Currently, the formula always returns 0 so there is something that I don't understand...

##### MrExcel MVP
Oupss I made a small error copying the array formula:
=IF(\$C\$10:\$AV\$10="Balance",IF(\$C\$11:\$AV\$11="USD",COLUMN(\$A\$1:\$AV\$1),),)

Suppose that:
C10 = Balance
D10 = Balance
E10 = Movement
and
D11 = USD
E11 = USD

I would expect the fomula to return 4, which is the first column number that meets both conditions. Currently, the formula always returns 0 so there is something that I don't understand...
Control+shift+enter, not just enter:

=MIN(IF(\$C\$10:\$AV\$10="Balance",IF(\$C\$11:\$AV\$11="USD",COLUMN(\$C\$10:\$AV\$10))))

#### lovallee

##### Board Regular
Re: Issue with multiple conditions and column() - SOLVED

Now I understand.

1) Removing the "value_if_false" argument of the IF functions inserts FALSE values rather than zeros in the resulting array (i.e. {FALSE, 4, FALSE, ...} rather than {0, 4, 0, ...}

2) The MIN function extracts the value "4" from the resulting array {FALSE, 4, FALSE, ...}.

Louis