Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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 |
#2
|
|||
|
|||
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 |
#3
|
|||
|
|||
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 |
#4
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel option to store trendline's coefficients in cells for use | Charts and Charting in Excel | |||
Help with an array function | New Users to Excel | |||
Array Function Question | Excel Worksheet Functions | |||
HLookup? or an array function?? | Excel Worksheet Functions | |||
Dynamic Function Reference Question | Excel Discussion (Misc queries) |