Issue with multiple conditions and column()

lovallee

Board Regular
Joined
Jul 8, 2002
Messages
216
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

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,139
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
Joined
Jul 8, 2002
Messages
216
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

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,139
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

Board Regular
Joined
Jul 8, 2002
Messages
216
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
 

Forum statistics

Threads
1,085,693
Messages
5,385,224
Members
401,936
Latest member
stephenpoff

Some videos you may like

This Week's Hot Topics

Top