DocAElstein
Banned user
- Joined
- May 24, 2014
- Messages
- 1,336
<o
></o>
<o> </o>
. Hi,<o></o>
. I have done my best to keep this short and to the point, and probably failed, sorry! . But I think, however, a formula expert could follow ( - I did at least answer half my question as I tried to clearly explain my problem!! )<o></o>
. I am having difficulty understanding part of a formula which I am using to develop a much larger formula. I can get over the problem, but without understanding what is going on it makes further developing and maintaining of the formula difficult. <o></o>
. Can someone explain, in detail if possible, what is actually “going on” here<o></o>
. I simplify the example greatly to demonstrate and emphasis I do not need a “solution” to get the formula to “work”. - I have that . I need to know exactly what is happening in the formula as it stands in it’s current shortened and simplified form. <o></o>
<o> </o>
………………………..<o></o>
<o> </o>
. In the following screenshot I am primarily interested in the Table J2:K6 as my input , and the row L2:R2 as my output. The output is the pulled out values in the K column for consecutive rows in the Input table if in that row the value in the J column is J2 <o></o>
<o> </o>
<o> </o>
<o> </o>
<o> </o>
Using Excel 2007<o></o>
[Table="width:, class:grid"][tr][td]-[/td][td]></o>
[/table][Table="width:, class:grid"][tr][td]MrExcelExample[/td][/tr][/table]<o></o>
<o> </o>
<o> </o>
<o> </o>
. I achieve this with the following formula which is “CSE” entered in cell L2 and is then dragged across through to cell R2.<o></o>
<o> </o>
<o> </o>
<o> </o>
. Full screen shot showing all formulas:<o></o>
<o> </o>
. <b>Excel 2007</b><table width="1" cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6"><colgroup><col width="25px" style="background-color: #E0E0F0" /><col /><col /><col /><col /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: #E0E0F0;text-align: center;color: #161120"><th></th><th>J</th><th>K</th><th>L</th><th>M</th><th>N</th><th>O</th><th>P</th><th>Q</th><th>R</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">2</td><td style=";">J2</td><td style=";">K2</td><td style="background-color: #F0F5E7;;">K2</td><td style="background-color: #F0F5E7;;">K3</td><td style="background-color: #F0F5E7;;"></td><td style="background-color: #F0F5E7;;">K5</td><td style="background-color: #F0F5E7;;"></td><td style="text-align: right;background-color: #F0F5E7;;">#BEZUG!</td><td style="text-align: right;background-color: #F0F5E7;;">#BEZUG!</td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style=";">J2</td><td style=";">K3</td><td style="text-align: right;background-color: #F0F5E7;;"></td><td style="text-align: right;background-color: #F0F5E7;;"></td><td style="text-align: right;background-color: #F0F5E7;;"></td><td style="text-align: right;background-color: #F0F5E7;;"></td><td style="text-align: right;background-color: #F0F5E7;;"></td><td style="text-align: right;background-color: #F0F5E7;;"></td><td style="text-align: right;background-color: #F0F5E7;;"></td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style=";">OLE</td><td style=";">K4</td><td style="text-align: right;background-color: #F0F5E7;;"></td><td style="text-align: right;background-color: #F0F5E7;;"></td><td style="text-align: right;background-color: #F0F5E7;;"></td><td style="text-align: right;background-color: #F0F5E7;;"></td><td style="text-align: right;background-color: #F0F5E7;;"></td><td style="text-align: right;background-color: #F0F5E7;;"></td><td style="text-align: right;background-color: #F0F5E7;;"></td></tr><tr ><td style="color: #161120;text-align: center;">5</td><td style=";">J2</td><td style=";">K5</td><td style="text-align: right;background-color: #F0F5E7;;"></td><td style="text-align: right;background-color: #F0F5E7;;"></td><td style="text-align: right;background-color: #F0F5E7;;"></td><td style="text-align: right;background-color: #F0F5E7;;"></td><td style="text-align: right;background-color: #F0F5E7;;"></td><td style="text-align: right;background-color: #F0F5E7;;"></td><td style="text-align: right;background-color: #F0F5E7;;"></td></tr><tr ><td style="color: #161120;text-align: center;">6</td><td style=";">WigyWam</td><td style=";">K6</td><td style="text-align: right;background-color: #F0F5E7;;"></td><td style="text-align: right;background-color: #F0F5E7;;"></td><td style="text-align: right;background-color: #F0F5E7;;"></td><td style="text-align: right;background-color: #F0F5E7;;"></td><td style="text-align: right;background-color: #F0F5E7;;"></td><td style="text-align: right;background-color: #F0F5E7;;"></td><td style="text-align: right;background-color: #F0F5E7;;"></td></tr></tbody></table><p style="width:8,4em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #A6AAB6;border-top:none;text-align: center;background-color: #E0E0F0;color: #161120">MrExcelExample</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF" ><tr><td style="padding:6px" ><b>Array Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #A6AAB6"><thead><tr style=" background-color: #E0E0F0;color: #161120"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">L2</th><td style="text-align:left">{=INDEX(<font color="Blue">IF(<font color="Red">$J2=$J2:$J$6,$K2:$K$6,""</font>),COLUMNS(<font color="Red">$J:J</font>),1</font>)}</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">M2</th><td style="text-align:left">{=INDEX(<font color="Blue">IF(<font color="Red">$J2=$J2:$J$6,$K2:$K$6,""</font>),COLUMNS(<font color="Red">$J:K</font>),1</font>)}</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">N2</th><td style="text-align:left">{=INDEX(<font color="Blue">IF(<font color="Red">$J2=$J2:$J$6,$K2:$K$6,""</font>),COLUMNS(<font color="Red">$J:L</font>),1</font>)}</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">O2</th><td style="text-align:left">{=INDEX(<font color="Blue">IF(<font color="Red">$J2=$J2:$J$6,$K2:$K$6,""</font>),COLUMNS(<font color="Red">$J:M</font>),1</font>)}</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">P2</th><td style="text-align:left">{=INDEX(<font color="Blue">IF(<font color="Red">$J2=$J2:$J$6,$K2:$K$6,""</font>),COLUMNS(<font color="Red">$J:N</font>),1</font>)}</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">Q2</th><td style="text-align:left">{=INDEX(<font color="Blue">IF(<font color="Red">$J2=$J2:$J$6,$K2:$K$6,""</font>),COLUMNS(<font color="Red">$J:O</font>),1</font>)}</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">R2</th><td style="text-align:left">{=INDEX(<font color="Blue">IF(<font color="Red">$J2=$J2:$J$6,$K2:$K$6,""</font>),COLUMNS(<font color="Red">$J:P</font>),1</font>)}</td></tr></tbody></table><b>Entered with Ctrl+Shift+Enter.</b> If entered correctly, Excel will surround with curly braces {}.<o></o>
<b>Note: Do not try and enter the {} manually yourself</b></td></tr></table><br /><o></o>
<o> </o>
<o> </o>
<o> </o>
. If for example I look at the formula from cell N2 in the formula bar, highlight my True/False conditioning bit $J2=$J2:$J$6 and hit F9 , I get my instant evaluation of that part of the Formula shown in the Formula Bar thus<o></o>
<o> </o>
=INDEX(IF({TRUE,TRUE,FALSE,TRUE,FALSE},$K2:$K$6,""),COLUMNS($J:L),1)<o></o>
<o> </o>
. Clearly ( or at least I thought!? ) My formula becomes <o></o>
<o> </o>
=INDEX("",COLUMNS($J:L),1), which I was expecting to error because of the “” argument for the look up Array in the first argument of the INDEX Function ( INDEX ( “” , 3 , 1 ) )<o></o>
<o> </o>
. Indeed this formula, and variations thereof, ( Array entered or not ) <o></o>
= INDEX("",COLUMNS($J:L),1) … etc.<o></o>
. do indeed error rather than returning an empty cell as was the case with my original formula<o></o>
<o> </o>
. Question <o></o>
. can anyone explain why my original formula returns an empty cell and not an error ?<o></o>
<o> </o>
……………………………………………<o></o>
<o> </o>
. Further. … I have good reasons in my massive formula I am developing to drag past column Q ( That is to say effectively “looking past WigyWam” row , even though nothing past there is of interest..Clearly as I go past Column Q my row index for the INDEX goes past the maximum dimension of 5 and so I expect an error again. This time I get the expected error!! So I thought I had it sussed!!
<o></o>
<o> </o>
Thanks<o></o>
Alan Elston<o></o>
<o> </o>
<o> </o>
P.s. 1. I drop of a file if it helps. (XL 2007) “_Concatenating a) 2007.xlsx”<o></o>
https://app.box.com/s/di2hyk051cdnpx04qg6sqv1eswnx524y<o></o>
P.s.2 I emphasize Again that Ihave a solution, so for anyone with a similar requirement popping by I show it here..<o></o>
<o> </o>
From XL 2007<o></o>
For XL2003 or lower (and from XL 2007)<o></o>
… again array entered in cell L2 and dragged across<o></o>
P.s.3 I realize I can remove the ;1 column index in all the above formulas for the INDEX and rely on the default 1, but it just helps me to keep track of what is going on<o></o>
></o> <o
> </o>. Hi,<o
></o>. I have done my best to keep this short and to the point, and probably failed, sorry! . But I think, however, a formula expert could follow ( - I did at least answer half my question as I tried to clearly explain my problem!! )<o
></o>. I am having difficulty understanding part of a formula which I am using to develop a much larger formula. I can get over the problem, but without understanding what is going on it makes further developing and maintaining of the formula difficult. <o
></o>. Can someone explain, in detail if possible, what is actually “going on” here<o
></o>. I simplify the example greatly to demonstrate and emphasis I do not need a “solution” to get the formula to “work”. - I have that . I need to know exactly what is happening in the formula as it stands in it’s current shortened and simplified form. <o
></o><o
> </o>………………………..<o
></o><o
> </o>. In the following screenshot I am primarily interested in the Table J2:K6 as my input , and the row L2:R2 as my output. The output is the pulled out values in the K column for consecutive rows in the Input table if in that row the value in the J column is J2 <o
></o><o
> </o><o
> </o><o
> </o><o
> </o>Using Excel 2007<o
></o>[Table="width:, class:grid"][tr][td]-[/td][td]
I
[/td][td]J
[/td][td]K
[/td][td]L
[/td][td]M
[/td][td]N
[/td][td]O
[/td][td]P
[/td][td]Q
[/td][td]R
[/td][/tr][tr][td]1
[/td][td]INDEX(__,r/c)[/td][td][/td][td][/td][td]c=Row(J:L) = 1[/td][td]c=Row(J:M) = 2[/td][td]c=Row(J:N) = 3[/td][td]c=Row(J:O) = 4[/td][td]c=Row(J:P) = 5[/td][td]c=Row(J:Q) = 6[/td][td]c=Row(J:R) = 7[/td][/tr][tr][td]2
[/td][td]r = 1[/td][td]J2[/td][td]K2[/td][td]K2[/td][td]K3[/td][td][/td][td]K5[/td][td][/td][td]#BEZUG!
[/td][td]#BEZUG!
[/td][/tr][tr][td]3
[/td][td]r = 2[/td][td]J2[/td][td]K3[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr][tr][td]4
[/td][td]r = 3[/td][td]OLE[/td][td]K4[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr][tr][td]5
[/td][td]r = 4[/td][td]J2[/td][td]K5[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr][tr][td]6
[/td][td]r = 5[/td][td]WigyWam[/td][td]K6[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr][tr][td]7
[/td][td]r = 6[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr][tr][td]8
[/td][td]r = 7[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]<o></o>[/table][Table="width:, class:grid"][tr][td]MrExcelExample[/td][/tr][/table]<o
></o><o
> </o><o
> </o><o
> </o>. I achieve this with the following formula which is “CSE” entered in cell L2 and is then dragged across through to cell R2.<o
></o><o
> </o>=INDEX( IF($J2=$J2:$J$6 , $K2:$K$6 , "") , COLUMNS($J:J) , 1 )<o></o>
></o>> </o><o
> </o>. Full screen shot showing all formulas:<o
></o><o
> </o>. <b>Excel 2007</b><table width="1" cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6"><colgroup><col width="25px" style="background-color: #E0E0F0" /><col /><col /><col /><col /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: #E0E0F0;text-align: center;color: #161120"><th></th><th>J</th><th>K</th><th>L</th><th>M</th><th>N</th><th>O</th><th>P</th><th>Q</th><th>R</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">2</td><td style=";">J2</td><td style=";">K2</td><td style="background-color: #F0F5E7;;">K2</td><td style="background-color: #F0F5E7;;">K3</td><td style="background-color: #F0F5E7;;"></td><td style="background-color: #F0F5E7;;">K5</td><td style="background-color: #F0F5E7;;"></td><td style="text-align: right;background-color: #F0F5E7;;">#BEZUG!</td><td style="text-align: right;background-color: #F0F5E7;;">#BEZUG!</td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style=";">J2</td><td style=";">K3</td><td style="text-align: right;background-color: #F0F5E7;;"></td><td style="text-align: right;background-color: #F0F5E7;;"></td><td style="text-align: right;background-color: #F0F5E7;;"></td><td style="text-align: right;background-color: #F0F5E7;;"></td><td style="text-align: right;background-color: #F0F5E7;;"></td><td style="text-align: right;background-color: #F0F5E7;;"></td><td style="text-align: right;background-color: #F0F5E7;;"></td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style=";">OLE</td><td style=";">K4</td><td style="text-align: right;background-color: #F0F5E7;;"></td><td style="text-align: right;background-color: #F0F5E7;;"></td><td style="text-align: right;background-color: #F0F5E7;;"></td><td style="text-align: right;background-color: #F0F5E7;;"></td><td style="text-align: right;background-color: #F0F5E7;;"></td><td style="text-align: right;background-color: #F0F5E7;;"></td><td style="text-align: right;background-color: #F0F5E7;;"></td></tr><tr ><td style="color: #161120;text-align: center;">5</td><td style=";">J2</td><td style=";">K5</td><td style="text-align: right;background-color: #F0F5E7;;"></td><td style="text-align: right;background-color: #F0F5E7;;"></td><td style="text-align: right;background-color: #F0F5E7;;"></td><td style="text-align: right;background-color: #F0F5E7;;"></td><td style="text-align: right;background-color: #F0F5E7;;"></td><td style="text-align: right;background-color: #F0F5E7;;"></td><td style="text-align: right;background-color: #F0F5E7;;"></td></tr><tr ><td style="color: #161120;text-align: center;">6</td><td style=";">WigyWam</td><td style=";">K6</td><td style="text-align: right;background-color: #F0F5E7;;"></td><td style="text-align: right;background-color: #F0F5E7;;"></td><td style="text-align: right;background-color: #F0F5E7;;"></td><td style="text-align: right;background-color: #F0F5E7;;"></td><td style="text-align: right;background-color: #F0F5E7;;"></td><td style="text-align: right;background-color: #F0F5E7;;"></td><td style="text-align: right;background-color: #F0F5E7;;"></td></tr></tbody></table><p style="width:8,4em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #A6AAB6;border-top:none;text-align: center;background-color: #E0E0F0;color: #161120">MrExcelExample</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF" ><tr><td style="padding:6px" ><b>Array Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #A6AAB6"><thead><tr style=" background-color: #E0E0F0;color: #161120"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">L2</th><td style="text-align:left">{=INDEX(<font color="Blue">IF(<font color="Red">$J2=$J2:$J$6,$K2:$K$6,""</font>),COLUMNS(<font color="Red">$J:J</font>),1</font>)}</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">M2</th><td style="text-align:left">{=INDEX(<font color="Blue">IF(<font color="Red">$J2=$J2:$J$6,$K2:$K$6,""</font>),COLUMNS(<font color="Red">$J:K</font>),1</font>)}</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">N2</th><td style="text-align:left">{=INDEX(<font color="Blue">IF(<font color="Red">$J2=$J2:$J$6,$K2:$K$6,""</font>),COLUMNS(<font color="Red">$J:L</font>),1</font>)}</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">O2</th><td style="text-align:left">{=INDEX(<font color="Blue">IF(<font color="Red">$J2=$J2:$J$6,$K2:$K$6,""</font>),COLUMNS(<font color="Red">$J:M</font>),1</font>)}</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">P2</th><td style="text-align:left">{=INDEX(<font color="Blue">IF(<font color="Red">$J2=$J2:$J$6,$K2:$K$6,""</font>),COLUMNS(<font color="Red">$J:N</font>),1</font>)}</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">Q2</th><td style="text-align:left">{=INDEX(<font color="Blue">IF(<font color="Red">$J2=$J2:$J$6,$K2:$K$6,""</font>),COLUMNS(<font color="Red">$J:O</font>),1</font>)}</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">R2</th><td style="text-align:left">{=INDEX(<font color="Blue">IF(<font color="Red">$J2=$J2:$J$6,$K2:$K$6,""</font>),COLUMNS(<font color="Red">$J:P</font>),1</font>)}</td></tr></tbody></table><b>Entered with Ctrl+Shift+Enter.</b> If entered correctly, Excel will surround with curly braces {}.<o
></o><b>Note: Do not try and enter the {} manually yourself</b></td></tr></table><br /><o
></o><o
> </o><o
> </o><o
> </o>. If for example I look at the formula from cell N2 in the formula bar, highlight my True/False conditioning bit $J2=$J2:$J$6 and hit F9 , I get my instant evaluation of that part of the Formula shown in the Formula Bar thus<o
></o><o
> </o>=INDEX(IF({TRUE,TRUE,FALSE,TRUE,FALSE},$K2:$K$6,""),COLUMNS($J:L),1)<o
></o><o
> </o>. Clearly ( or at least I thought!? ) My formula becomes <o
></o><o
> </o>=INDEX("",COLUMNS($J:L),1), which I was expecting to error because of the “” argument for the look up Array in the first argument of the INDEX Function ( INDEX ( “” , 3 , 1 ) )<o
></o><o
> </o>. Indeed this formula, and variations thereof, ( Array entered or not ) <o
></o>= INDEX("",COLUMNS($J:L),1) … etc.<o
></o>. do indeed error rather than returning an empty cell as was the case with my original formula<o
></o><o
> </o>. Question <o
></o>. can anyone explain why my original formula returns an empty cell and not an error ?<o
></o><o
> </o>……………………………………………<o
></o><o
> </o>. Further. … I have good reasons in my massive formula I am developing to drag past column Q ( That is to say effectively “looking past WigyWam” row , even though nothing past there is of interest..Clearly as I go past Column Q my row index for the INDEX goes past the maximum dimension of 5 and so I expect an error again. This time I get the expected error!! So I thought I had it sussed!!
<o></o><o
> </o>Thanks<o
></o>Alan Elston<o
></o><o
> </o><o
> </o>P.s. 1. I drop of a file if it helps. (XL 2007) “_Concatenating a) 2007.xlsx”<o
></o>https://app.box.com/s/di2hyk051cdnpx04qg6sqv1eswnx524y<o
></o>P.s.2 I emphasize Again that Ihave a solution, so for anyone with a similar requirement popping by I show it here..<o
></o><o
> </o>From XL 2007<o
></o>=IFERROR(INDEX(IF($J2=$J2:$J$6,$K2:$K$6,""),COLUMNS($J:J),1),"")<o></o>
></o>></o>=IF(ISERROR(INDEX(IF($J2=$J2:$J$6,$K2:$K$6,""),COLUMNS($J:J),1)),"",INDEX(IF($J2=$J2:$J$6,$K2:$K$6,""),COLUMNS($J:J),1))<o></o>
></o>></o>P.s.3 I realize I can remove the ;1 column index in all the above formulas for the INDEX and rely on the default 1, but it just helps me to keep track of what is going on<o
></o>
