Index/Match - Reducing Calculation Speed

[The Great SrH]

Hi guys,

I'm hoping you can help reduce the amount of resources my spreadsheet is currently using.

I think the main culprit is my "Individual Report" tab which has in total, 57 cells with an Index/Match formula in. An example of the code is below:

=INDEX(ObsData!$L:$L,MATCH(1,INDEX(($J$6=ObsData!$G:$G)*($J$7=ObsData!$H:$H),0,1),0))

As you can see, the function is searching the ObsData tab for a couple of search criteria's and returning the result.

I've read that it's better to not search an entire column however, the amount of rows in the ObsData tab will continue to grow as the days go on.

I've included a screenshot so you can see what the tab looks like.

Any tips or ideas?!

Thanks

 

[arthurbr]

Use an Excel Table as data source ?
Use dynamic ranges ?

 

[The Great SrH]

Use an Excel Table as data source ?
Use dynamic ranges ?

Apologies I completely missed this response!

I don't know if they will be able to pull the specifics of what my Index Match is doing (it's based on atleast 2 criterias depending what's selected on the home tab)

Really sorry but i'm not the most familiar with what you suggested!

 

[Alex Blakenburg]

What version of Excel are you running ? For dynamic arrays you need to have Microsoft 365.
If you are not sure type =xlookup( into a cell and tell us if it recognizes it.

Is there any reason you can't convert your data on ObsData into an Excel Table ?
If you do that you can use Table Structured Referencing which will automatically expand as you add data and you won't need to do a Full Column scan.