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
 

Excel Facts

Can a formula spear through sheets?
Use =SUM(January:December!E7) to sum E7 on all of the sheets from January through December

BarryL

Well-known Member
Joined
Jan 20, 2014
Messages
1,436
Office Version
  1. 2019
Platform
  1. Windows
  2. MacOS
would a SUMIFS work?

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

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…
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,163,785
Messages
5,833,690
Members
430,222
Latest member
Nickkarl

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