INDEX/MATCH lookup without ARRAY function

Barslund

New Member
Joined
Dec 22, 2016
Messages
4
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!

SPHdNcj.png
 

Some videos you may like

Excel Facts

Enter current date or time
Ctrl+: enters current time. Ctrl+; enters current date. Use Ctrl+: Ctrl+; Enter for current date & time.

Barslund

New Member
Joined
Dec 22, 2016
Messages
4
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…
 

Watch MrExcel Video

Forum statistics

Threads
1,122,694
Messages
5,597,571
Members
414,156
Latest member
WDMix

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top