INDEX/MATCH lookup without ARRAY function

[Barslund]

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!

 

[BarryL]

would a SUMIFS work?

=SUMIFS(H:H,G:G,$M4,J:J.N$3)

 

[Barslund]

would a SUMIFS work?

=SUMIFS(H:H,G:G,$M4,J:J.N$3)

<style type="text/css">p.p1 {margin: 0.0px 0.0px 0.0px 0.0px; font: 12.0px Helvetica}p.p2 {margin: 0.0px 0.0px 0.0px 0.0px; font: 12.0px Helvetica; min-height: 14.0px}</style>That worked, nice and simple solution!


Unfortunately it didn’t speed up the calculation time, actually extended it - much to my surprise.


Must find an alternative way to work with larger datasets, as this won’t be the last time.


PowerBI coming up…

 

[Aladin Akyurek]

Would you post this in an Excel readable form?