# Array differences?

#### Excelor

##### New Member
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!

### Excel Facts

Do you hate GETPIVOTDATA?
Prevent GETPIVOTDATA. Select inside a PivotTable. In the Analyze tab of the ribbon, open the dropown next to Options and turn it off

#### AliGW

##### Banned
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

##### New Member
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).

Last edited:

##### MrExcel MVP
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

##### New Member
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

Last edited:

#### AliGW

##### Banned
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.

Last edited:

#### Excelor

##### New Member
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!

##### MrExcel MVP
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

##### New Member
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!

##### MrExcel MVP
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...

Replies
3
Views
302
Replies
1
Views
84
Replies
18
Views
759
Replies
4
Views
68
Replies
2
Views
108

1,191,554
Messages
5,987,245
Members
440,086
Latest member
Mahi786

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back