Index match excel

Soldato
Joined
22 Nov 2007
Posts
4,113
Sorry if this is a dumb question but i’m not near my pc right now. I was chatting to
Someone laat night about index match and i was saying how i put a 0 for the final index arguement e.g.

=index(b1:b10,match(c2,’e1:e10,0),0)

I assume even though its redundant that final 0 can’t be skewint the results returned in the index formula as its only indexing one column right? Secondly, don’t column arrays start from 1 in excel? But the 0 doesn’t cause an error.
 
Associate
Joined
25 May 2010
Posts
361
I don't have access to excel at the moment either but from microsofts site, it mentions for column_num (the part you're 0'ing):

"If you set row_num or column_num to 0 (zero), INDEX returns the array of values for the entire column or row, respectively. To use values returned as an array, enter the INDEX function as an array formula."

So entering 0 isn't redundant, it does do something.
 
Soldato
OP
Joined
22 Nov 2007
Posts
4,113
I don't have access to excel at the moment either but from microsofts site, it mentions for column_num (the part you're 0'ing):

"If you set row_num or column_num to 0 (zero), INDEX returns the array of values for the entire column or row, respectively. To use values returned as an array, enter the INDEX function as an array formula."

So entering 0 isn't redundant, it does do something.
Tried it on excel on ios earlier, doesnt make a difference with or without the 0
 
Soldato
OP
Joined
22 Nov 2007
Posts
4,113
Might perform differently even if it results in the same data. Try it on a ton of data and see if u can tell the difference.

Will do , bevause im only indexing one column apart from throwing an error i don’t know what it would do because excel columns start from 1 right?

If my index array was > 1 i guess it would do something different
 
Last edited:
Associate
Joined
25 May 2010
Posts
361
Will do , bevause im only indexing one column apart from throwing an error i don’t know what it would do because excel columns start from 1 right?

If my index array was > 1 i guess it would do something different
it's the same as if you are sorting it or filtering it - as there's just one row, there wont be any visible changes but it is still doing something and adding more data will skew it, so it's better practice to not do it :)
 
Soldato
OP
Joined
22 Nov 2007
Posts
4,113
it's the same as if you are sorting it or filtering it - as there's just one row, there wont be any visible changes but it is still doing something and adding more data will skew it, so it's better practice to not do it :)
adding more colums to the index array? Agree

I managed to check my work today where i used that extra 0 and fortunately the results were the same when removing the 0, phew. It was about 1500 lines
 
Back
Top Bottom