Index match excel

Bassmansam · 28 May 2022 at 09:55

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.

Miganto · 28 May 2022 at 20:00

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.

Bassmansam · 28 May 2022 at 23:51

Miganto said:
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

throwaway4372 · 29 May 2022 at 00:00

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.

Bassmansam · 29 May 2022 at 00:20

throwaway4372 said:
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

throwaway4372 · 29 May 2022 at 08:36

Bassmansam said:
excel columns start from 1 right?

Looks that way based on images here:

INDEX Function

The Excel INDEX function returns the value at a given location in a range or array. You can use INDEX to retrieve individual values, or entire rows and columns. The MATCH function is often used together with INDEX to provide row and column numbers.

exceljet.net

Miganto · 30 May 2022 at 21:21

Bassmansam said:
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

Bassmansam · 30 May 2022 at 22:24

Miganto said:
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