INDEX Formula expected to error. Errors sometimes. Sometimes returns empty cell. Help from Formula expert needed.

[DocAElstein]

<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>​

For XL2003 or lower (and from XL 2007)<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>​

… 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>

 

[LloydFinancials]

Doc,

Click F9 on just the IF statement {=IF($J2=$J2:$J$6,$K2:$K$6,"")} and you get the array used in the INDEX: ={"K2";"K3";"";"K5";""}.

Luke

 

[DocAElstein]

Doc,

Click F9 on just the IF statement {=IF($J2=$J2:$J$6,$K2:$K$6,"")} and you get the array used in the INDEX: ={"K2";"K3";"";"K5";""}.

Luke

Hi Luke,

. Thanks for the reply.
. But that does not EXPLAiN anything to me.
. I am a beginner, but I do understand that this formula ( Normal , not CSE )


Using Excel 2007
[TABLE="class: grid"]
<tbody>[TR]
[TD]-[/TD]
[TD]

L​

[/TD]
[/TR]
[TR]
[TD]

2​

[/TD]
[TD]=INDEX({"K2";"K3";"";"K5";""},COLUMNS($J:J),1)[/TD]
[/TR]
</tbody>[/TABLE]
[TABLE="class: grid"]
<tbody>[TR]
[TD]MrExcelExample[/TD]
[/TR]
</tbody>[/TABLE]

... dragged across gives me the results I obtain.
. That is the basic INDEX formula, using an Array, rather than a spreadsheet range, as first argument
. I am trying to figure out HOW that argument is obtained

.. But thanks again for the reply

Alan

 

[LloydFinancials]

The if statement is looking for J2 in J2:J6 and returning the value in Col K where it is true and a "" where false. Since you want to return an array K2:K6 it has to be entered as an array formula Ctrl+Shft+Entr. Then the F9 is just showing what it is returning. I hear your confusion. I have still not got whole logic behind array formulas. Sorry I could not have been more help.

 

[DocAElstein]

....... Sorry I could not have been more help.

,,... every bit helps, I appreciate it..
... I am sort of getting it, .. slowly... I'm cluttering up the Test Forum just now.. checking / correcting formulas - I have the extra confusion of having English and German , ; . conventions … that don't help...

...... I hear your confusion. I have still not got whole logic behind array formulas. ......

.... Phew.... That helps. At least I am not alone on this one...

http://www.mrexcel.com/forum/excel-...se-curly-bracket%94-you-have-input-array.html

Thanks Again
Alan

 

[DocAElstein]

Hi ,<o></o>
. I think I have answered my original question from Post #1, based on what I think is at least a fairly good Laymen’s understanding of the whole “CSE” thing..<o></o>
. I have tried to summarize as briefly as possible my conclusions. I would be very grateful for some comments, in particular it would be very helpful if someone is at least able to confirm to me that I have “got it right”. <o></o>
… My somewhat crude explanation would be as follows:…<o></o>
<o> </o>
…. Possibly the whole “CoverStorieE” thing has some other secrets which few people ( if any still living ) are privy to… <o></o>
<o> </o>
. 1) We have the first aspect , The idea that CSE “ties down” or maintains the correct reference to Arrays - and somehow “coerces” Excel to do array type analysis.<o></o>
….. in this case I have a set of 5 rows and “sort of” 2 columns , that is to say the 2 ranges: a Range of Booleans $J2=$J2:$J$6 ; and $K2:$K$6 a normal Range with some values in it ( “K2” , “K3” etc… ) . A row by row analysis is done. This returns me an Array as required for the first argument of my INDEX( . Exactly how the form of this Array comes about is what was causing my problems in understanding the output in my original example.<o></o>
. I explain this now as follows..<o></o>
<o> </o>
. 2a) Functions are treated separately and have new rules when used in conjunction with CSE stuff. The differences can be very subtle, hardly noticeable, so no documentation exists…. Really we should have a new name for them . Here for example – we do not have an IF Function. – we have a new function IFcse<o></o>
Function .. ….. following Luke’s suggestion, from Post #2, but taking out the third ( “Else” ) argument in the IF Statement, I have a slightly modified version of my original CSE Formula thus:<o></o>
<o> </o>
.Formula ( CSE Type ) : <o></o>
<o> </o>
=INDEX(IF($J2=$J2:$J$6,$K2:$K$6),COLUMNS($J:J),1)<o></o>
<o> </o>
<o> </o>
.. using the “ Highlight-part-of-Formula-in-Formula-Bar ,then-Hit-F9-trick “ to get an instant evaluation, we see for the complete IF statement ( IF($J2=$J2:$J$6,$K2:$K$6) ) <o></o>
{"K2","K3",FALSE,"K5",FALSE} <o></o>
<o> </o>
<o> </o>
… correspondingly spreadsheet results <o></o>
<o> </o>
<o> </o>
UsingExcel 2007<o></o>
[Table="width:,class:grid"][tr][td]-[/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]

2​

[/td][td]J2[/td][td]K2[/td][td]K2[/td][td]K3[/td][td]

FALSE​

[/td][td]K5[/td][td]

FALSE​

[/td][td]

#BEZUG!​

[/td][td]

#BEZUG!​

[/td][/tr][tr][td]

3​

[/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]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]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]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][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr][tr][td]

8​

[/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(2)[/td][/tr][/table]<o></o>
<o> </o>
. The subtle but important difference between IF and IFcse……..<o></o>
… a) if a no valid pairing is obtained, the Function returns ( similar to its spreadsheet similar function IF ) a “FALSE” BUT it actually puts that word “FALSE” as string value in as a default into the Array ( Similarly in the spreadsheet IF Function , the word “FALSE” would be put in the cell in which the IF statement was. ) <o></o>
… b) the third argument does not work similarly to the spreadsheet IF. It has a completely different syntax : It actually replaces the default “FALSE” in the Array with whatever you choose to put there. ( This is done for as many times as the FALSE is obtained )<o></o>
……<o></o>
….c) . This is effectively the outcome of a) and b). The “Thing” returned from a IFcse Statement will (Always I think , but maybe there are exceptions ????? ) be an Array, and therefore a valid first argument for the INDEX, ) <o></o>
<o> </o>
… I would prefer to do the following<o></o>
………………………………………….<o></o>
Use This formula ( CSE type )<o></o>
<o> </o>
=INDEX(IF($J2=$J2:$J$6,$K2:$K$6,$J$10:$J$14),COLUMNS($J:J),1)<o></o>
<o> </o>
Corresponding to this Spreadsheet<o></o>
<o> </o>
UsingExcel 2007<o></o>
[Table="width:,class:grid"][tr][td]-[/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]

2​

[/td][td]J2[/td][td]K2[/td][td]K2[/td][td]K3[/td][td]empty[/td][td]K5[/td][td]False[/td][td]

#BEZUG!​

[/td][td]

#BEZUG!​

[/td][/tr][tr][td]

3​

[/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]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]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]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][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr][tr][td]

8​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr][tr][td]

9​

[/td][td]DefaultValues[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr][tr][td]

10​

[/td][td]Anything[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr][tr][td]

11​

[/td][td]Iwont for[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr][tr][td]

12​

[/td][td]empty[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr][tr][td]

13​

[/td][td]or[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr][tr][td]

14​

[/td][td]False[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr][/table][Table="width:,class:grid"][tr][td]MrExcelExample(3)[/td][/tr][/table]<o></o>
<o> </o>
<o> </o>
. The reason I prefer to do this, is because I am staying with the .a) bit of CSE , - keeping the “row by row type analysis ” / Arrays idea , and not relying on some default implicit specific to the IFcse<o></o>
…………………………………………………………………………………………<o></o>
<o> </o>
. Thanks for Reading!! The above was what I would like to get some clarification as to its correctness. In other word “has the Mut Got it !!”<o></o>
<o> </o>
Thanks again<o></o>
Alan<o></o>
…………………………………………………….<o></o>
<o> </o>
Just to put something back if anyone is learning or getting anything form this thread…<o></o>
<o> </o>
If my above explanations are correct, which effectively says / confirms wot I found regarding when the errors occurred in the original example , - that is to say the “error handling”, as I loosely call it, is only needed for the “overshoot” when the columns / Rows exceed that determined by the end of the table, …..That being the case then an alternative would be to simply extend the INDEX first argument ( Grid / (Array) / Range ) to some arbitrary large amount, say 234 so….<o></o>
<o> </o>
=INDEX(IF($J2=$J2:$J$234 , $K2:$K$234 , $J$10:$J$242 ),COLUMNS($J:J),1)<o></o>
<o> </o>
….. this does not affect the working of the formula but does remove the need for that error handling which , especially for the case of the <= XL2003 versions of the code can be somewhat messy.<o></o>
<o> </o>
… Note , importantly to put a =”” in your (also necessarily range extended ) default Values ( or else for empty cells you will get a 0 )<o></o>
<o> </o>
<o> </o>
UsingExcel 2007<o></o>
[Table="width:,class:grid"][tr][td]-[/td][td]

J​

[/td][/tr][tr][td]

9​

[/td][td]DefaultValues[/td][/tr][tr][td]

10​

[/td][td]Anything[/td][/tr][tr][td]

11​

[/td][td]I wont for[/td][/tr][tr][td]

12​

[/td][td]empty[/td][/tr][tr][td]

13​

[/td][td]or[/td][/tr][tr][td]

14​

[/td][td]False[/td][/tr][tr][td]

15​

[/td][td]=""[/td][/tr][tr][td]

16​

[/td][td]=""[/td][/tr][tr][td]

17​

[/td][td]=""[/td][/tr][tr][td]

18​

[/td][td]=""[/td][/tr][tr][td]

19​

[/td][td]=""[/td][/tr][tr][td]

20​

[/td][td]=""[/td][/tr][tr][td]

21​

[/td][td]=""[/td][/tr]<o></o>
[/table][Table="width:,class:grid"][tr][td]MrExcelExample(3)[/td][/tr][/table]<o></o>
<o> </o>
<o> </o>
Alan<o></o>
………………………………………………………………………………<o></o>
<o> </o>
P.s. 1<o></o>
I have updated the example file The link to which was given in Post #1 and is still valid.<o></o>
<o> </o>
P.s. 2<o></o>
I have made and will update some light hearted notes for myself on the whole CSE Curly Bracket Stuff. FWTFIW here is the link to my Doc. Word File: ( XL 2007 “CSEWhistleBlower.doc” )<o></o>
https://app.box.com/s/avk6paydbtame1hz7ge5zenh6ll1p35e<o></o>
Alan.<o></o>

 

[steve the fish]

Let me try to explain.

=INDEX(IF($J2=$J2:$J$6,$K2:$K$6,""),COLUMNS($J:J),1)

The IF part, if entered CSE, is producing the array {TRUE;TRUE;FALSE;TRUE;FALSE} which in turn becomes {"K2";"K3";"";"K5";""}

Note this is the value in K2 not the cell reference. As 'K2' is in cell K2 it returns K2!

So our formula is now =INDEX({"K2";"K3";"";"K5";""},COLUMNS($J:J),1)

This becomes =INDEX({"K2";"K3";"";"K5";""},1,1) which is "K2"

Then dragged right the next cell formula becomes:

=INDEX({"K2";"K3";"";"K5";""},COLUMNS($J:K),1)

or =INDEX({"K2";"K3";"";"K5";""},2,1) which is "K3"

Hope that helps.

 

[DocAElstein]

Let me try to explain.

..........

Hi Steve,
. Many thanks for the reply. I appreciate it.
. I am totally in agreement with your explanation.
. It is a very good and clear analysis of what is going on based on what I call the ……
“ Highlight-part-of-Formula-in-Formula-Bar ,then-Hit-F9-trick to get an instant evaluation “
. This clearly exposes all the Arrays as they are worked out within the formula. And these Arrays you reproduced for me ( possibly you copied from the Formula Bar and pasted into the Post as I did. )
. Thanks for taking that effort.
.
. However. I was trying to take it a bit further in an attempt to understanding how the whole CSE stuff works by explaining how those Array are obtained.
.
. And my question was as to whether my more detailed explanation was correct.

. But thanks very much for taking the time to read and reply. It is a difficult compromise, I find, to give enough detail for someone to help without making the Post too long to read. As my post was a bit long it is inevitable that you may have missed the actual question I was asking.
. So thanks again
.
. Alan

 

[steve the fish]

The array produced is the result of these formula:

IF(J2=J2,K2,"")
IF(J2=J3,K3,"")
IF(J2=J4,K4,"")
IF(J2=J5,K5,"")
IF(J2=J6,K6,"")

 

[DocAElstein]

The array produced is the result of these formula:........

Thanks again Steve, I do appreciate you taking the time to reply.......

.. But you are just repeating small bits of what I said. So you are, again, explaining clearly what happens, rather than how.
. Again my question was if my explanations of how Excel does this were correct, rather than just stating again what happens.

. Thanks again
Alan