Array differences?

[Excelor]

Hi

I'm not sure how to word this so bear with me!

I asked a while ago how to find the first instance of "" using MATCH, to which I got a great reply...

' =MATCH(TRUE,INDEX(A1:A20="",0),0) '

This works perfectly and is entered as a normal formula (rather than an array formula - Crtl+Shift+Enter).

My query is, what is the difference in using ' INDEX(A1:A20="",0) ' and simply ' A1:A20="" '?

Using ' =MATCH(TRUE,A1:A20="",0) ' gives an error. Although when I enter this as an array formula it works the same as the answer I was given above.

When evaluating the lookup_array section of the MATCH function (using F9) both ' INDEX(A1:A20="",0) ' and ' A1:A20="" ' return and array, e.g. FALSE; FALSE; TRUE; FALSE, etc, etc, etc.

So why when the array is return from using INDEX does it not have to be entered as an array formula? I can't find the answer to this anywhere, maybe because i'm not sure what to search for. However, I think, and hope, the answer is quite interesting!


..........................................Afterthought...................................
Just a thought as I was typing this! Is it something to do with INDEX having an array as it's first "parameter" (if that's why they're called in Excel)? Does this expect to return an array where-as with MATCH the first parameter is not an array? NOT SURE!?! Please help me understand!

 

[AliGW]

MATCH needs an array as the second argument:

=MATCH(lookup_value,lookup_array,match_type)

As a matter of interest, does this work?

=MATCH("",A1:A20,0)

 

[Excelor]

Thanks for the reply. I think you've missed my query. "My query is, what is the difference in using ' INDEX(A1:A20="",0) ' and simply ' A1:A20="" ' ?"

See, the MATCH function accepts an array as the second argument but both the above return an array. What I want to know is why does using the first of these work however using simply ' A1:A20="" ' has to be entered as an array formula?

=MATCH("",A1:A20,0) doesn't work. This is why I had asked the question a while ago because I was confused as to why it never. The solution I was given was a way around it but now I am trying to understand how/why it works. I think I can make sense on how it works but I can't understand why you have to use the INDEX function to return the array that is then used in the MATCH function.

I did say bear with me, maybe I am not explaining my query that great!

Thanks

.................edit.......................
basically...
=MATCH(TRUE,INDEX(A1:A20="",0),0) works

=MATCH(TRUE,A1:A20="",0) works if entered as an array formula (it doesn't work if not).

 

[Aladin Akyurek]

I regret somewhat that I introduced this usage of INDEX. That is:

INDEX(Expression,0)

where Expression delivers a set of evaluations; 0 meaning all of the evaluations.

So,

INDEX(A1:A20="",0)

means: provide the whole set of evalutions as result.

Note that: INDEX(A1:A20="",0) is contracted version of INDEX(A1:A20,0,1). If we had INDEX(E1:H1="",0), the full version would be INDEX(E1:H1="",1,0).

{=MATCH(TRUE,A1:A20="",0)}

is faster than no cse...

=MATCH(TRUE,INDEX(A1:A20="",0),0)

while it is still an array-processing formula.

 

[Excelor]

Thanks for this. I'm not 100% sure that I understand.

Are you saying that INDEX(A1:A20="",0) does not return a 'true' array, rather a set of evaluations? Is this why it can be entered as a standard formula?

I am learning, I do not yet fully comprehend the entire Excel Jargon!

Thanks

 

[AliGW]

Thanks for the reply. I think you've missed my query.

Not entirely! I was replying to this:

Using ' =MATCH(TRUE,A1:A20="",0) ' gives an error.

 

[Excelor]

Ah okay, apologies then. I was stating that to explain my actual question, rather than asking why that returned an error! Sorry for the confusion!

 

[Aladin Akyurek]

Thanks for this. I'm not 100% sure that I understand.

Are you saying that INDEX(A1:A20="",0) does not return a 'true' array, rather a set of evaluations? Is this why it can be entered as a standard formula?

I am learning, I do not yet fully comprehend the entire Excel Jargon!

Thanks

When evaluated, A1:A20="" can result in something like {FALSE;TRUE;TRUE;FALSE;FALSE;...}. This evalution is fed to INDEX that makes them available because of 0.

 

[Excelor]

When evaluated, A1:A20="" can result in something like {FALSE;TRUE;TRUE;FALSE;FALSE;...}. This evalution is fed to INDEX that makes them available because of 0.

Okay I think I get you now. So, am I right in saying the reason you enter {} to make an array formula is so to get access to the item/info stored in the array? So because the array is already evaluated in INDEX and made available with the 0, I therefore don't have to make them available by entering the {}?

I'm sorry to keep pestering but the reason for my question was so I fully understood and I hope I am getting there in doing so! Please correct me if I am wrong at all!

Thanks for a great response!

 

[Aladin Akyurek]

Okay I think I get you now. So, am I right in saying the reason you enter {} to make an array formula is so to get access to the item/info stored in the array? So because the array is already evaluated in INDEX and made available with the 0, I therefore don't have to make them available by entering the {}?

I'm sorry to keep pestering but the reason for my question was so I fully understood and I hope I am getting there in doing so! Please correct me if I am wrong at all!

Thanks for a great response!

A1:A20="" | INDEX({FALSE;TRUE;TRUE;FALSE;FALSE;...},0) | {FALSE;TRUE;TRUE;FALSE;FALSE;...} which can be fed for example to MATCH...