Perhaps one of the most widely-believed myths surrounding INDEX is that it is not possible to coerce a return of more than one value from the array passed to it.

At first sight, and after various attempts at coercing such a return (including array-entry), it does indeed seem for all the world that INDEX cannot be so induced, and stubbornly persists in returning just the first element in any array generated.

Granted, the required coercions are not obvious (the first of which is borderline tautological: the necessary initial clause, IF(1,… – or any other suitable value in place of 1 – is self-evidently TRUE), though, for example, the following non-array formula:

=SUM(INDEX(A1:A10,N(IF(1,{1,4,8}))))

will sum the entries in cells A1, A4 and A8.

An alternative syntax (and again, non-array) is:

=SUM(INDEX(A1:A10,N(INDEX({1,4,8},,))))

We can extend this to two-dimensional ranges also:

=SUM(INDEX(A1:C10,N(IF(1,{1,4,8})),N(IF(1,{1,2,3}))))

is equivalent to:

=SUM(A1,B4,C8)

Furthermore, we can employ the standard technique of transposing one of the array constants to generate more varied results:

=SUM(INDEX(A1:C10,N(IF(1,{1,4,8})),N(IF(1,{1;2}))))

giving precisely the same result as:

=SUM(A1,A4,A8,B1,B4,B8)

or even generate arrays equivalent to those for which we might normally adopt OFFSET:

=SUM(INDEX(A1:C10,N(IF(1,{1,2,3})),N(IF(1,{1;2;2;3}))))

giving the same result as:

=SUM(SUBTOTAL(9,OFFSET(A1,,{0,1},3,2)))

which naturally is also equivalent to:

=SUM(A1:B3)+SUM(B1:C3)

Of course, in all of these examples my choice of SUM as the coercing function was purely arbitrary, and in 99% of cases this combination of SUM and INDEX can be achieved by much more straightforward means (e.g. with SUMIF(S) or an array version of SUM).

However, we are not by any means restricted to passing the resulting array to SUM, and I have found many practical and interesting applications of this ability to generate more than one return using INDEX. Unlike OFFSET, for example, for which the first parameter *must* be a reference to an actual range in the worksheet, INDEX can also accept – and manipulate – for its reference arrays which consist of values generated e.g. via other subfunctions within the formula.

And the use of this technique of coercing more than one return is not restricted to INDEX. For example, attempting to find the maximum value in K1:K10 where the corresponding row entry in J1:J10 is the first occurrence in that range of either "A", "B" or "C", you might be disappointed upon seeing that the array formula:

=MAX(VLOOKUP({"A","B","C"},J1:K10,2,0))

does not always give the desired result, the reason being (and you can see this if you go through it with *Evaluate Formula*) that the formula resolves, undesirably, to:

=MAX(VLOOKUP("A",J1:K10,2,0))

i.e. only the first element in the array of three is processed by VLOOKUP.

However, using the coercion outlined above, the non-array:

=MAX(VLOOKUP(T(IF(1,{"A","B","C"})),J1:K10,2,0))

does the job nicely (also note the necessary use of T() here, and not N()).

I leave it to the reader to discover the many other possibilities yielded by this technique, both using INDEX and other functions.

If what you want refuses to be coerced by array-entry alone, try this method and you might just be in luck!

Pingback: Daily Dose of Excel » Blog Archive » An INDEX of insights from ExcelXOR

Hi XOR. I note that the array {1,4,8} has to be hard coded in order for this to work. If you try to generate it programatically, it fails. Which is a shame, because I was hoping to use this approach to let users choose elements of the INDEX themselves, rather than hard-code them into the formula.

Do you know of any way around this?

Hi Jeff.

I only chose those values at random to illustrate the technique. What precisely are you wanting? How would you envisage, as you say, letting “

users choose elements of the INDEX themselves“? Can you give me an example?Cheers

Sure can. I’ll email it to you…hard to explain.

Pingback: XIRR returns zero when returns are negative

Pingback: How to use LOGEST function with data sets that has Blank cell

Pingback: Anonymous

Pingback: Excel Magic Trick 1308: Concatenate Items in Rows & Columns from Two-Way Table with TEXTJOIN | Microsoft Home

Pingback: Excel Magic Trick 1308: Concatenate Items in Rows & Columns from Two-Way Table with TEXTJOIN | Microsoft Home

Pingback: Excel Magic Trick 1309: Extract Unique List and List in Single Cell: TEXTJOIN Array Formula | Microsoft Home

Pingback: Excel Magic Trick 1311: Return Multiple Items from One Lookup Value & Show Total for Invoice Items | Microsoft Home

Pingback: Excel Magic Trick 1312: Count The Number Of Mondays & Tuesdays Since Birth: NETWORKDAYS.INTL | Microsoft Home

Not seen it documented anywhere but believe it or not, this works in the latest versions of excel

Pingback: Excel Magic Trick 1316: VLOOKUP with Multiple Lookup Tables: IF, INDIRECT, SWITCH, IFS, or CHOOSE? | Microsoft Home

@LeoThanks, but I believe that construction has always worked in Excel.

Also, INDEX is not returning an

arrayof values there; in fact, the two INDEX functions in your construction are both resolving to just a single value each (in this case a range reference).Regards

@ XOR LX

That is totally true! I got a bit overexcited as I found this usage shortly after seeing your site and it solved my conundrum as it basically allows a range (or contiguous array) to be passed to something else without using the volatile OFFSET. Yes I have to use two INDEXs and it has to be a contiguous array, but as I said, rarely documented although looks like it’s been around for ages! Wish I’d known about it earlier… Either way, if I want to do something more elaborate your method is now in my toolkit, thanks for all the great tips!

@LeoCouldn’t agree more on principle, and you’re to be congratulated for being one of the few who have seen the light!

A quick look around the various Excel forums, and sadly also Excel blogs, reveals a preponderance for inferior OFFSET or INDIRECT set-ups, whereas, as you’ve shown, a perfectly good, non-volatile (well, nearly!) option with INDEX is available.

Cheers

Pingback: Excel Magic Trick 1306: Simulate VLOOKUP Helper Column In Array Formula using T(IF({1},Array) Trick

Hi

I have seen that if I have , for example , a spreadsheet such, –

// I J K

22 A – 1

23 B – 2

24 C – 3

, – then this formula, in a spreadsheet cell , –

, – returns me in the spreadsheet a single value of 1.

Furthermore, If I select that formula in the Formula Bar, and hit F9 , then I am given the evaluated value of 1

It would appear that the first argument of VLOOKUP is only seeing “A”.

If I adjust the formula using the techniques discussed in this Blog to this, –

, – then , If I select this formula in the Formula Bar, and hit F9 , then I am given the evaluated value of {1;2;3}

It would appear that the first argument is now seeing all three values of “A” ”B” and ”C”

The above is all in agreement with the results discussed in this Blog.

My Questions are:

I would love to know, how, and why the trick of

“works” to give the multiple values ???

I would also like to know why it is that for the case of VLOOKUP, the function does not without this trick “see” the Array. ???

Does anyone know the answer to this ???

or

Was the trick , T(IF(1, ____ )) , simply found by trial and error and experimenting ???, and therefore nobody understands what is going on here ???

I have spent many days trying to understand what is going on here. I am going slightly mad. .. Lol.. 😦 🙂 .

Can anyone save my sanity.

Thanks

Alan

Hi again,

I had a couple of typos in my formulas, – they should have read

VLOOKUP({“A”;”B”;”C”},I22:K24,3, 0)

and

VLOOKUP(T(IF(1, {“A”;”B”;”C”})),I22:K24,3,0)

But my questions remain the same !

Alan

Hi again,

I think I may be getting a bit closer to what is happening here….

I find in experimenting that fundamentally a simple Range reference such as =A1:K1 ( in a spreadsheet as well as in VBA , ) seems to initially return a Range object, after which a fundamental .Value Property is applied in many situations to return values “housed” in Variant types.

Index similarly appears to follow this rule.

These observations are not always easy in a spreadsheet to observe. Using the Evaluate(“ “), which , I believe, uses some of the same processes in evaluating formulas in a spreadsheet, can be more revealing.

Consider such a code snippet

Dim Rnga_sn As Range

Set Rnga_sn = Evaluate(“=A1:K1”)

Dim Arra_sn() as Variant

Let Arra_sn() = Rnga_sn.Value ‘ returns Variant/Variant(1 to 1, 1 to 11)

We can in fact achieve the same usually by

Let Arra_sn = Evaluate(“=A1:K1”) ‘ returns Variant/Variant(1 to 1, 1 to 11)

The important point here is that we are “relying” on a default .Value Property ( which many Experts regard as a Method ) to do this extra step. This may not always take place.

It has further been my experience that Worksheet Functions work somewhat less efficiently on Arrays than on Ranges. Therefore in some functions there may be some extra coding to prevent the default .Value converting a Range reference into an Array.

Considering the same example but with these code lines

Let Arra_sn() = Evaluate(“=transpose(transpose(A1:K1))”) ‘Variant/Variant(1 to 11)

‘ Set Rnga_sn = [transpose(transpose(A1:K1))] ‘ Error ‘424’ Object required

We note that the last line errors. I am thinking the Transpose is programmed differently such as to “force a final evaluation of the values ( for the spreadsheet this is done after any formatting is taken from the initially Transposed Range object ) . In this particular case we note also that, as is with the transpose Function, it will convert a “vertical” 2 Dimensional Array to a 1 Dimensional Array.

A one Dimensional Array is a more “internal” type thing, at a more “deeper” level of processes.. That might also be significant.

So I have a possible explanation for the use of Transpose , T( ) . This has that extra final step that forces the .Value type evaluation to return an Array.

I am not quite sure of the significance and need for IF(1, ____ )

Or maybe I do. 🙂

I can do any of the tricks ( which I can explain the working of in detail ) which force multivalve analysis… for example these works also in ( Evaluate(“ “) )

IF(Row(), ____ )

Index( _____ ,0,0)

But as I suggested this returns fundamentally a Range object. Further evaluating of the values is blocked for the reasons I mentioned. As I described the T ( ) then does this extra needed last evaluation. It has also been observed that IF(1, ____ ) does not always work, and that IF({1}, ____ ) is then required. This I can also explain: For a simple Array, the IF(1, ____ ) is sufficient as that will also evoke multi value analysis . It is only required to have IF( to kick in any analysis. For other things it will be necessary to have a term in the first argument of IF( , ____ ) that evokes multi cell analysis.

I have a feeling I have it

🙂

Alan

Or maybe a lot of that last is rubbish as T() is not Transpose … 😦

This combination appears to have been stumbled upon by several individuals in the past and it’s nice of Xor Lx to share this trick here. I think Alan’s on the right lines as to why the T(IF(1 is required, below is an attempt to make the analysis more precise…

1. First let’s try to express functions in terms of their constituent data types (v[alues], a[rrays] and/or r[anges]). By testing each input type in the function and applying the various formula tools one can build a comprehensive list – a kind of “periodic table of functions” if you like – which then makes it easy to spot patterns as below…

VLOOKUP va(v, var, var, [v])

INDEX var(ar, v, [v], [v])

IF var(va, [var], [var])

N v(vr)

T v(vr)

Take VLOOKUP as an example.

i. Without arguments the function wizard shows that all inputs can take values (number, text, logical or any) so “v” is placed as argument as well as result type. The last argument is not shown in bold in the wizard so we enclose with [.]

ii. With a {1} entered in each argument of the function wizard the middle two arguments display an array and the other two display values as well as the result type so an “a” is placed in the corresponding position.

iii. Entering =SUM(VLOOKUP(A1,A1,A1,A1)) and stepping through the formula evaluate tool leaves only the middle two arguments as references and the result is a value so “r” is placed in the middle two arguments only.

(As shown in the previous response the Evaluate function can also be used here to help determine argument/result types either from a defined name or vba.)

2. Based on the return type one can distinguish between two types of formula result when an array type is expected:

i. Array Evaluation: {v(.)} a function is evaluated multiple times (once for each value argument) and the result consists of the array of return values.

ii. Array Coercion: va(.) An array is returned by applying a function once only. Value arguments are not coerced to arrays.

INDEX, VLOOKUP and IF apply array coercion when CSE is applied and value arguments are therefore not array evaluated. N and T on the other hand do apply array evaluation to arguments.

3. Based on the argument types one sees that some function arguments take value only inputs and others also allow for references.

i. Value type arguments automatically convert any cell references to values prior to evaluating the function. As such they are restricted to value only operations and cannot handle large reference operations or multiple area references effectively.

ii. Reference type arguments allow the function to be able to process more general reference types [examples may include Sheet2!A:XFD, (A1:D1, A3:D3), Sheet1:Sheet3!A1:E4]. Reference type arguments also allow functions access to other cell attributes such as row height and formatting (eg SUBTOTAL, AGGREGATE, CELL.)

Passing a reference type result containing an array to a reference type value argument like T(IF(1,{.}) or T(INDEX({.},) forces an array evaluation i.e. { (vr) var }. Analysis of the full list of functions shows there are many other combinations that could also be inserted here. In effect the array is passed “by reference” instead of “by value” as would normally be the case.

Hi Lori,

Thanks very much for this.

Actually I made some major mistakes in my previous posts.

For example, as I am actually very new to Excel Formulas I very very stupidly Mistook T() for Transpose().!! My reasoning suggested Transpose( IF( __ , __ ) ) should work, which I then found didn’t ??

( I posted a couple of follow ups but they have not come through ?? )

So I learnt about and used the Formula Wizard and formula evaluate tool for the first time 🙂

I am following most of 1, but have not quite grasped the relevance yet to the final issues. I will continue to practice with the Formula Wizard and formula evaluate tool and I may get the point.

2.

ii. is not making any sense to me yet. I apologise, but I do not understand fully what you are saying.

This is how I am seeing things in those terms

i Array Evaluation: As you say, some Functions return arrays. They have been programmed to do that. It may or may not be normal for them to take an array or Range reference in an argument or argument , ( multi value argument or argumants ) .

_……

( I do not know exactly what a CSE type 1 does, I have some theories. But in any case, I expect a Function programmed to return Arrays will somehow make use of those processes. This process is, I believe a sort of Shifting in a Controlled ( Control led Shift ing ! 🙂 ! ) way, before Entering, allowing each of the multi values to be made in turn as if each value were a single argument entry. – The updating of a Worksheet needs to be paused and a shifted frame of reference made for each value before the final evaluation done on hitting Enter. I used the term CSE for this, before I had ever heard anything of Excel Array Formula Entry, CSE. ( CSE type 2 is simply selection of an extended multi cell spreadsheet range to use as an output display for the returned array of values resulting from the CSE process ). I note here that in VBA Evaluate(“ “), no such requirement exists. But when applied by the user, CSE is relating to ii Array Coercion )

_…

ii. Array Coercion: Some Functions are not programmed to return arrays. However, in most case, where a single value argument is “normal”, we may enter in the place of that single argument, an Array or range reference.

At this point, I am failing as yet, to follow your explanations.

3. I am seeing some interstin ideas from what you say in 3.

4.

Let me try now my thoughts for a simpler example.

I will use again the same simple spreadsheet range

//- I J K

22 A – 1

23 B – 2

24 C – 3

I want to use the

Rept( String , RepeatNumber )

Function to return

AA

BB

4.i. Inspreadsheet.

For the normal single value argument use:

For AA it is simple

=Rept(“A”,2) or =Rept(I22,2)

For the multi value ( array or reference ) argument case:

In a spreadsheet =REPT({“A”;”B”},2) does not error , but to return ( or “see” ) both values we must use CSE type 2 to define a range of cells to use to display the output.

In the spreadsheet =REPT(I22:I23,2) will error without CSE for all but entry in rows 22 and 23 . (My theory is that without CSE, the normal evaluation in most cells done at each cell as the screen updates is messed up, – the internal “Help values matrix” is only defined for all columns in those two rows, hence Controlled Shifting of the reference needs to be done for each cell reference before then Evaluate on Enter as if it were a single reference )

4.ii. In formula bar, F9 Evaluation Select formula in Formula bar and hit F9 ( Hit Esc or Ctrl+z to get back to formula view)

Type =REPT({“A”;”B”},2) or =REPT(I22:I23,2) in any cell. Select formula in Formula bar and hit F9 ( Hit Esc or Ctrl+z to get back to formula view) In formula bar, F9 Evaluation , we see {“AA”;”BB”}

4.iii. In VBA Evaluate(“ “).

I believe Evaluate(“ “) comes very close to doing what is done on hitting Enter in a spreadsheet. But we do not have the problem with needing CSE.

This , Evaluate(“=REPT(I22:I23,2)”) , will not error. But Rept is not programmed to return multi values. My thoughts are that {“AA”;”BB”} is possibly “there”. But the same basic process as in the multi cell analysis takes place but is limited to the first, ( top left ) analysis of “all column , then next row” analysis ).

This , Evaluate(“=REPT({“”A””;””B””},2)”) , will also just returns the first value by the same reasoning.

I believe I need to somehow make the full multi value analysis of “all columns then next row” take place. Then those available extra values will be “seen” and used.

Various ways can be done to make this happen. I am happy with all these solutions which I can explain in detail

Evaluate(“=Index(REPT({“”A””;””B””},2),0,0)”)

Evaluate(“=Transpose(REPT({“”A””,””B””},2))”) ‘ Note: , in place of ;

Evaluate(=If(Row(1:2),REPT({“”A””;””B””},2)))

Evaluate(=If(Column(),REPT({“”A””;””B””},2)))

Evaluate(=If({1},REPT({“”A””;””B””},2)))

I am happy that this does not work

Evaluate(“=If(1,REPT({“”A””;””B””},2))”)

This does not work. *My first reaction is not too be surprised. I have not yet seen any documentation to tell me that T( ) does array evaluation_*…

Evaluate(“=T(REPT({“”A””;””B””},2))”)

_*… I fully expect I am in error, but my thoughts are currently slightly against yours, ( or possibly I am not quite meaning the same as you… ) :

5.

_ I am thinking ( very likely wrongly ) that

5.i. Index( __ , 0 , 0 ) coerces Array analysis because somehow it is programmed to “make” an Array from what it sees in its first argument.

Indeed, I saw something very interesting at snb’s Array site just now ( which I cannot fully understand ) which suggests something very special is going on in Index to make the first argument an Array :

arrRw1() = Array(“a”, “c”): arrRw2() = Array(“c”, “d”)

Dim arrArr(1 To 1, 1 To 2) As Variant

arrArr(1, 1) = arrRw1(): arrArr(1, 2) = arrRw2()

Dim arr2x2() As Variant

Let arr2x2() = Application.Index(arrArr(), 0, 0)

‘ Thhe above code snippet turns {{“a”, “c”},{“c”, “d”}} into {“a”, “c”; “c”, “d”} ???

5.ii. Transpose I explained in my post MARCH 26, 2017 AT 15:00

5.iii. _ If ( ___ , ___ ) on its own is not useful, but

_ If ( SomeArrayReturning Thing , _____ ) will “switch” in the multi value analysis.

5.iv. _ VLookUp …. as in my post MARCH 14, 2017 AT 17:07 …

And further:

6.

6.i. In the spreadsheet VLOOKUP(I22:I24,I22:K24,3,0) behaves as expected. ( Errors ( other than in rows 22 – 24 ) . It “works” with CSE )

6.ii. F9 Formula Bar evaluation returns the single value 1

6.iii. _ Similarly in Evaluate(“ “) with any of the tricks to “see” any available Arrays, such as_..

Evaluate(“=If(Row(),VLOOKUP(I22:I24,I22:K24,3,0))”)

Evaluate(“=If(Row(),VLOOKUP({“”A””;””B””;””C””},I22:K24,3,0))”)

Evaluate(“=Index(VLOOKUP({“”A””;””B””;””C””},I22:K24,3,0),0,0)”)

_..all these above returns {1} ( as a Double held in an array(1 to 1) of Variant type variable )

6.iv. _ Furthermore, use of the T(IF(1, ___ )) trick on its own _..

Evaluate(“=VLOOKUP(T(IF(1,{“”A””;””B””;””C””})),I22:K24,3,0)”)

_.. simply returns a simple Double Type 1

6.v. Finally , something of this form gives the final working formula

Evaluate(“=If(Row(),VLOOKUP(T(IF(1,{“”A””;””B””;””C””})),I22:K24,3,0))”) ‘ Returns {1;2;3} Doubles in Variants

_…………………………

7. So as before, my conclusion is that the_..

7.i. T(IF(1, ___ )) or T(IF(Row(), ___)) or T(Index( ___ ,0)) etc…

_.. is making that first argument be “seen” as an Array.

( 7.ii. And just to confuse things , T(Transpose({“”A””,””B””,””C””})) and T(Transpose(Transpose({“”A””;””B””;””C””}))) do not work – they return just {1} ??? )

I am still not , ( yet ) , seeing anything to explain this last “revealing of” that second Array. I think I know what is happening and probably apply to always get what I want.

I am not understanding why, ( yet )

_………………………..

8.i. BUT THANKS VERY MUCH again for your input, I will continue to review, and I may understand further.

I need to let what you have kindly contributed go through my head a few more times..

I will re comment if I make any further progress in understanding.

8.ii. I like your idea of that the answer may lie in that in some Functions a range reference may not be converted to values and so the array of values is then not “available”.

But outstanding issues there are:

8.ii.a) _ Possibly it could be expected that a line like this might error, rather than take a the first value of “A” in I22:I24, to finally return 1 ( Double in variant )

Evaluate(“=VLOOKUP(I22:I24,I22:K24,3,0)”)

8.ii.b) _ This line is not relying on evaluation of the values from a reference. Never the less, without the T( ……., ___ tricks it only returns the {1} from the single first evaluation

Evaluate(“=If(Row(),VLOOKUP({“”A””;””B””;””C””},I22:K24,3,0))”)

9. It is suggesting to me that some extra “blocking” of holding an Array there in first argument of VLOOKUP is applied. That I can accept as having been done. But why on earth , and how, should the T( ……., ___ tricks remove that blocking and other things not .

Once again , what on earth is T( ……., ___ doing.

Alan

10. P.s. Some conclusion I have, ( possibly a contributing suggestion ): .. ..

10.i. my preferred trick would be

T(IF(Row( __ , __ ) , _____ ))

or even

T(IF(Row( __ , __ ) * Column( ___ , ___ ) , _____ ))

This allows some extra “control” of what is going on, for example:

Evaluate(“=If(Row(),VLOOKUP(T(IF(Row(1:3),{“”A””;””B””;””C””})),I22:K24,3,0))”) ‘ Returns {1;2;3}

Evaluate(“=If(Row(),VLOOKUP(T(IF(Row(1:2),{“”A””;””B””;””C””})),I22:K24,3,0))”) ‘ Returns {1;2;Error 2042}

Evaluate(“=If(Row(),VLOOKUP(T(IF(Row(1:2)*column(A:B),{“”A””;””B””;””C””})),I22:K24,3,0))”) ‘ Returns {1;2;Error 2042,1;2;Error 2042}

(pgc01 at MrExcel I thank for that )

10.ii. The use of 1 in If(1,__ is possibly a bad idea. I have heard of cases where in that trick, and others, they did not work with a 1 . At the very least If({1},__ should be considered.

( Having said that, why it does work in the cases under consideration might help understand what is going on.. but I still have a ways to go on that… 😦 )

Pingback: Generating a Sorted Unique Array in #Excel using Only Formulas by David Hager | Excel For You

Pingback: #Excel Super Links #5 – shared by David Hager | Excel For You

Pingback: Using Conditional Formatting in #Excel to Highlight the N Closest Values to the Mean of a Range by David Hager | Excel For You

Pingback: Conditional Formatting Gem in #Excel: Highlight the N Closest Values to the Mean of a Range in a Filtered List by David Hager | Excel For You