INDEX and MATCH functions

[rasputin1970]

One of these days I was using INDEX+MATCH function in a large table (7800 rows and 30 columns) and one doubt came to my mind:

- In terms of CPU workload, memory, excel etc, is there any difference between INDEX(table_column C:C!;MATCH(1;(table_column F:F!!=xpto....) and INDEX(table_column C5:C76!;MATCH(1;(table_column F5:F67!!=xpto....)?

- Is there any limit in the number of INDEX functions that can be used in a Excel Workbook?

- Is there any limit in the "extension" of the INDEX function? see the formula I've used below:

=IFNA(CONCATENATE($F5;" ";IF($I5<>"";INDEX(Global!$F:$F;MATCH(1;(Global!$B:$B=$C5)*(Global!$C:$C=$F5)*(Global!$D:$D=CONCATENATE($D5;"_";$E5;I$3));0));IF($J5<>"";INDEX(Global!$F:$F;MATCH(1;(Global!$B:$B=$C5)*(Global!$C:$C=$F5)*(Global!$D:$D=CONCATENATE($D5;"_";$E5;J$3));0));INDEX(Global!$F:$F;MATCH(1;(Global!$B:$B=$C5)*(Global!$C:$C=$F5)*(Global!$D:$D=CONCATENATE($D5;"_";$E5;L$3));0))));" ";IF($I5<>"";INDEX(Global!$G:$G;MATCH(1;(Global!$B:$B=$C5)*(Global!$C:$C=$F5)*(Global!$D:$D=CONCATENATE($D5;"_";$E5;I$3));0));IF($J5<>"";INDEX(Global!$G:$G;MATCH(1;(Global!$B:$B=$C5)*(Global!$C:$C=$F5)*(Global!$D:$D=CONCATENATE($D5;"_";$E5;J$3));0));INDEX(Global!$G:$G;MATCH(1;(Global!$B:$B=$C5)*(Global!$C:$C=$F5)*(Global!$D:$D=CONCATENATE($D5;"_";$E5;L$3));0)))));"")

Thanks

 

[Jonmo1]

Definitely a difference. But not so much due to the index or match function.
It's more because of all the criteria expressions
(Global!$B:$B=$C5)

Index itself doesn't care about entire column references.
Match does a little.
But that setup forces excel to evaluate every single cell in column B.
You should definitely use the restricted range.

I understand the need to accommodate fluctuating range sizes.
You probably have a number where you can say "my data will surely never be more than xx rows".
Use that number +10%

You started by saying you have 7800 rows. Use 10000.
10K is MUCH better than a million.