Issue with multiple conditions and column()

[lovallee]

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

 

[Aladin Akyurek]

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]

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
C11 = CAD
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...

 

[Aladin Akyurek]

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

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

Thank you very much Aladin.

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, ...}.


Kind regards Aladin,

Louis