Great formula that provides exact index/match... but how does it work?

So a non-array formula acting like an array formula?

I did note that the formula does not require array entry.

Jonmo, thanks for the good explanation

Originally Posted by Jonmo1

Putting that in INDEX makes it return as an array of results

That isn't strictly correct, Jonmo

EXACT returns an array on its own, the second INDEX function isn't actually required at all, it's used here just to avoid array entry

This version works too but needs CTRL+SHIFT+ENTER

=INDEX(C1:C4,MATCH(TRUE,EXACT(A1,B1:B4),0))

Originally Posted by Jonmo1

Does it? Is that documented somewhere?

Hello Jonmo,

I'm not aware of any documentation but you can see it happen quite easily.

Just put the EXACT formula in a cell, e.g.

=EXACT(A1,B1:B4)

Now select the cell with the formula, press F2 key followed by F9 key and you can see the array that the formula returns, e.g. something like this:

={FALSE;FALSE;FALSE;TRUE}

Of course the array needs to be utilised within a function that can handle arrays to get any meaningful results, but INDEX isn't required for that. For example you can use this formula

=SUMPRODUCT((EXACT(A1,B1:B4)=TRUE)+0)

which will return a result from 0 to 4 depending on how many of the B1:B4 values have a case-sensitive match to A1

Originally Posted by Adison

What functions can be tricked via index(), so as not to require array entry (CTRL+SHIFT+ENTER)?

I think that pretty much any array, either just a calculation or involving a function, e.g. both these formulas return the first word in M1:M4 that contains a lower case "x"

=INDEX(M1:M4,MATCH(1,ISNUMBER(FIND("x",M1:M4))+0,0))

=INDEX(M1:M4,MATCH(1,INDEX(ISNUMBER(FIND("x",M1:M4))+0,0),0))

...but only the first one needs array entry

Senseis Jonmo and Daddylonglegs thanks again. Interesting exchange.

However, how does one know when a function has additional capabilities (array like capabilities) when embedded in an index() statement?

In this case, exact(x,y) behaves differently then index(exact(x,r1c1:r4c1),0)) behaves.

What other functions have this array like ability?

Hi
This whole business of getting multiple values from formulas has intrigued me on and off for some time. I started messing about with testing some explaining examples from about here:
http://www.excelforum.com/developmen...ml#post4589521
I hope I finally get the thing clear enough to do a Thread for the Sub Forum Tips and Tutorials ( https://www.excelforum.com/tips-and-tutorials/ )
I am almost there....

It is difficult to explain these things because I think it pretty well needs understanding close to exactly how Excel works. As MsLonglegs said, I do not think you will find much documentation, and even the Blog sites that talk around this subject which I recently visited, and got involved with in some detail, do not appear to give any detailed explanations, and I expect are often based on imperial observations.
_......

Originally Posted by Adison

Follow on question has to be -..
What functions can be tricked via index(), so as not to require array entry (CTRL+SHIFT+ENTER)?...
Or asked another way, which functions have dual abilities with the wrap of index()?

The point of all this is that the question is only partly about what functions can be “tricked” into doing the “Array like” stuff. It is closer to say that *****almost all functions have and give the “Array like” output without the Controlled Shifting before Entering. ( It is often partly more a question of you getting to “see” them ! )
If we are talking about functions that are not generally considered as returning multiple values, then in *****most cases the multiple values are there.
The way Excel works, ( Intercept Theory: I made that up myself .. lol... https://www.excelforum.com/tips-and-...d-vlookup.html ) , means that usually in most**** places in a spreadsheet a formula like =EXACT(A1,B1:B4) will Error because Excel needs to take each value in B1:B4 and pseudo do each one in its own full spreadsheet screen update. ( That is what CSE does: a sort of Controlled Shifting before doing an Enter). This results in an output with multiple values starting top left at the cell you put the formula in, and having relative positions of dimension and orientation of B1:B4.
Either CSE type 1 or CSE Type 2 will prevent the error as the CSE is done.
CSE type 2 simply defines the spreadsheet area in which to give the output. ( You would in the case of =EXACT(A1,B1:B4), need for CSE type 2, to select any 4 row x 1 column spreadsheet area before entering the formula in the formula bar, ( and followed then by CSE ) )
_......

I think we have 3 sort of similar “Evaluation” type things:
Whether we do either of these 3 things: _...
_Spreadsheet type formula evaluation
or
_ F9 evaluation in the Formula bar
or
_ VBA Evaluate(“ “)
_... we are , I suggest, based on my experiments, doing the same basic procedures , but possibly just slightly differently.

_ F9 evaluation in the Formula bar does a sort of CSE type 1 or 2 evaluation but has some additional coding to give the result in the formula bar. Hence we see a result for say, =EXACT(A1,B1:B4), even if it gives an error in most**** places in the spreadsheet.

_ VBA Evaluate(“ “) is intended to give the result you would if you put a formula or function in a spreadsheet cell. However we are not actually “in” a spreadsheet. Interception is therefore not used, as that process is only necessary for efficient screen updating, which is not required here . But the basic process of evaluation of multiple values is ether done or available to be done. The net result of this is that usually a formula like =EXACT(A1,B1:B4), in VBA like_..
Evaluate(“=EXACT(A1,B1:B4)”)
_.. will not error. But it will only return the result that you would get in a cell , which is a single value. There are many ways to, as they say, “coerce” multiple values. Index is just one of them.
To do this “coercing”, you just need to add to your formula, =EXACT(A1,B1:B4), something that does not affect the result, but will cause VBA to do multi value analysis.
Index(Array,0,2)
or
Index (Array,1,0)
is an allowed syntax to return, in these examples, either
the entire second “column”
or
the entire first “row” in Array.
So following on from that Index(Array,0,0) will return the entire Array. But the point is that it did multiple value analysis.
So generally doing
Index(EXACT(A1,B1:B4),0,0)
is one way in Evaluate(“ “) to get an array of values out.
_....

Now, all the above is , I think, a lot of what Jonmo1 was saying.

But I think I understand the point that Mslonglegs was saying. Indeed =EXACT(A1,B1:B4) is returning or rather has available an Array of values. But it relies on Interception Theory to return those which requires spreadsheet interaction which messes it up ( unless a Controlled Shifting before each individual 4 Enters are done to get each value ).
In post #11 , I think the idea is to demo functions whereby an Array is returned “internally” as it were, without Interception. You see SUMPRODUCT I think in this case , ( Post # 11 ) , is a function which does normally take an Array as argument. It is “wired” to accept an Array. It does not rely on Interception to return the Array. I think here, ( Post # 11 ), the sum will be made of each match, in my case example below screen shot {True;True;False;False} exact match to True . This would try to sum True and True, if not for the +0 which I think is a trick to change true to 1. Then that formula, _..

=SUMPRODUCT((EXACT(A1,B1:B4)=TRUE)+0)

_.. using data in my example screen shot below returns a value of 1 + 1 = 2
The point is here that the above Formula “looked” immediately like this to Excel:

=SUMPRODUCT(({TRUE;TRUE;FALSE;FALSE}=TRUE)+0)

or

=SUMPRODUCT((EXACT("Produnt Name",{"Produnt Name";"Produnt Name";"4";"0"})=TRUE)+0)

It looked immediately like that as SUMPRODUCT is “wired” to “turn” a reference of B1:B4 in EXACT(A1,B1:B4) into an Array



In your case you are not trying to get , or are not needing to do any “coercing” to get multiple values out into you spreadsheet or from VBA Evaluate(“ “). .... - This will work for example, ( in VBA ),
Evaluate("=INDEX(C1:C4,MATCH(TRUE,EXACT(A1,B1:B4),0),1)")
( It only returns one value. That is all you want. )

But_... Your formula is not “wired” to turn a reference of B1:B4 in EXACT(A1,B1:B4) into an Array
_....In your case, .. looking again at Jonmo1’s formula,
=INDEX(C1:C4,MATCH(True,INDEX(EXACT(A1,B1:B4),0,1),0),1)
( Noting I am being explicit where for Index(Array,row) , I use Index(Array,row,1) which gives the same results )
And consider for this Spreadsheet of values:
Using Excel 2007 32 bit

Row\Col	A	B	C
1	Produnt Name	Produnt Name	Chocolate-europe aroma
2	Given Look Up Value	Produnt Name	Type CS Entry
3	Chocolate-europe aroma	4	4
4	Chocolate-Cookies	0	0

Worksheet: XLORX

This ( via formula bar F9 ),
EXACT(A1,B1:B4)
Gives you
True
True
False
False
or {True;True;False;False}

And this:
INDEX(EXACT(A1,B1:B4),0,1)
Also gives you this:
True
True
False
False
or {True;True;False;False}

( The rest of the formula I think you follow:
_ Match of True to all that gives the first “hit” which is the first “Produnt Name” returning for match “position along “ of 1.
_ That in turn gives finally Index(C1:C4,1,1) which returns “Chocolate-europe aroma” )



But this_...
=INDEX(C1:C4,MATCH(True,INDEX(EXACT(A1,B1:B4),0,1),0),1)
_..works for you:

Whereas this _..
=INDEX(C1:C4,MATCH(True,EXACT(A1,B1:B4),0),1)
_.. does not work: ( without CSE: ( With CSE type 1 it works ) )

_.......
So why is that?
What I am suggesting, is that the extra Index( ,0,1) somehow is evoking the internal F9 evaluation to return that Array of values such that your final formula in a cell is effectively “seen” by Excel as like: _...
=INDEX(C1:C4,MATCH(True,{True;True;False;False},0),1)
-.. You will find that this last formula will also work without CSE when placed in any cell

_._________________________________

But it is all quite a difficult a question, or rather the answer is dependent on so many factors that a simple answer cannot be given. And, as I am just tackling now, there are some functions like *****VLookUp that need special treatment as they do not follow the “normal trend” of how these things work. So you really have to analyse all cases fully individually

I will probably have a clearer answer when I complete my post on this and do a Thread for the Sub Forum Tips and Tutorials.


Please comment

Alan



P.s. **** You will find that =EXACT(A1,B1:B4) will not error in rows 1 to 4. That is ( Implicit ) Intersection Theory. – That one I did not make up, but I had a go at explaining it as a special case of my made up Interception theory: https://www.excelforum.com/tips-and-...ml#post4575460