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" <[email protected]> wrote
in message news:[email protected]...
>
> 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
>

[sunfish62]

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]

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

[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" <[email protected]> wrote
in message news:[email protected]...
>
> 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
>

[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" <[email protected]> wrote
in message news:[email protected]...
>
> 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
>

[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" <[email protected]> wrote
in message news:[email protected]...
>
> 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
>

[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" <[email protected]> wrote
in message news:[email protected]...
>
> 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
>

[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" <[email protected]> wrote
in message news:[email protected]...
>
> 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
>

[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" <[email protected]> wrote
in message news:[email protected]...
>
> 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
>

[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" <[email protected]> wrote
in message news:[email protected]...
>
> 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
>

[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" <[email protected]> wrote
in message news:[email protected]...
>
> 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
>

[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" <[email protected]> wrote
in message news:[email protected]...
>
> 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
>

[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" <[email protected]> wrote
in message news:[email protected]...
>
> 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
>

[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" <[email protected]> wrote
in message news:[email protected]...
>
> 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
>