Hi,
I have two tables of data that I'm trying to sort into a matrix, were I'm currently using an index/match array function for each separate cell, which work, but the matrix has 90.000 cells, so the sheet takes 25min to calculate - not optimal.
The sheet looks like the picture below.
Using the following array formula for N4:
INDEX($H$4:$H$48248;MATCH($M12&N$3;$G$4:$G$48248&$J$4:$J$48248;0))
For simplicity with a smaller dataset this could translate into:
INDEX(H4:H40;MATCH(M4&N3;G4:G40&J4:J40;0))
So im first trying to match column "kundenr." in G with M, and then "uge" in column with "uge" in row 3.
This works fine if I use the above mentioned formula as an array formula for each separate cell, but how can I complete the same task while avoiding the array function, so calculations are completed faster for the around 90.000 cells in the matrix?
Thanks!
I have two tables of data that I'm trying to sort into a matrix, were I'm currently using an index/match array function for each separate cell, which work, but the matrix has 90.000 cells, so the sheet takes 25min to calculate - not optimal.
The sheet looks like the picture below.
Using the following array formula for N4:
INDEX($H$4:$H$48248;MATCH($M12&N$3;$G$4:$G$48248&$J$4:$J$48248;0))
For simplicity with a smaller dataset this could translate into:
INDEX(H4:H40;MATCH(M4&N3;G4:G40&J4:J40;0))
So im first trying to match column "kundenr." in G with M, and then "uge" in column with "uge" in row 3.
This works fine if I use the above mentioned formula as an array formula for each separate cell, but how can I complete the same task while avoiding the array function, so calculations are completed faster for the around 90.000 cells in the matrix?
Thanks!
