# INDEX/MATCH lookup without ARRAY function

#### Barslund

##### New Member
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!

### 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
would a SUMIFS work?

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

#### Barslund

##### New Member
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…

##### MrExcel MVP
Would you post this in an Excel readable form?

Replies
25
Views
267
Replies
3
Views
110
Replies
12
Views
597
Replies
5
Views
170
Replies
1
Views
740

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.

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.

### Which adblocker are you using?

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

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