Array Function Question
 

I am new to these forums, but comfortable with Excel generally. I just
discovered the Array functions, and am trying to figure out why the
following calculation doesn't work:

{=IF(C20:C30="C",$A20:$A30, "")}

Basically, if the entry in one of the cells in C column has "C", I want
the corresponding cell from the A column. The formula works if the
*first* cell in the range is "C"; otherwise, it doesn't.

I am sure that this is a simple fix. Can someone fill me in?

TIA,
David


--
sunfish62
------------------------------------------------------------------------
sunfish62's Profile: http://www.excelforum.com/member.php...o&userid=24541
View this thread: http://www.excelforum.com/showthread...hreadid=381356

you need to
make your cell references absolute
enclose the condition with an OR (so that any "C" in the range )and
if you need corresponding values then select the range a20:a30 first and
type this formula
=IF(or(C$20:C$30="C"),$A20:$A30,"")
array entered

"sunfish62" wrote
in message ...

I am new to these forums, but comfortable with Excel generally. I just
discovered the Array functions, and am trying to figure out why the
following calculation doesn't work:

{=IF(C20:C30="C",$A20:$A30, "")}

Basically, if the entry in one of the cells in C column has "C", I want
the corresponding cell from the A column. The formula works if the
*first* cell in the range is "C"; otherwise, it doesn't.

I am sure that this is a simple fix. Can someone fill me in?

TIA,
David


--
sunfish62
------------------------------------------------------------------------
sunfish62's Profile:
http://www.excelforum.com/member.php...o&userid=24541
View this thread: http://www.excelforum.com/showthread...hreadid=381356

Thanks for the quick reply. When I make the change you suggest, I am
*always* given the value in the first row of the range. What I want is
to get the *corresponding* A column cell. So, if the 'C' is in row 23,
give me A23.


--
sunfish62
------------------------------------------------------------------------
sunfish62's Profile: http://www.excelforum.com/member.php...o&userid=24541
View this thread: http://www.excelforum.com/showthread...hreadid=381356

I figured out what feels like a kludge, but it works. Here it is:

=INDIRECT(CONCATENATE("A", 19 + MATCH("C", C$20:C$30, 0)))

The Match gives me the position in the search array. I add the base
position of the array to that, concatenate the result with the column I
want, and pull the contents from the cell identified by the string.

Maybe that'll help someone else.

David


--
sunfish62
------------------------------------------------------------------------
sunfish62's Profile: http://www.excelforum.com/member.php...o&userid=24541
View this thread: http://www.excelforum.com/showthread...hreadid=381356