Array Function Question

[sunfish62]

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

 

[N Harkawat]

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]

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

 

[sunfish62]

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