formula or macro

[Miller83]

Hello,
I have a training record sheet which I am pulling data from to a matrix table I am currently using the below formula but it continually calculates threads. some assistance to speed this up please.
There are about 12000 cells that run this same formula which looks for the competency (Column C) and name (Column A) from one sheet and matches them on a second sheet returning an overdue or current status from column "G"
=IFNA(INDEX('Training Records'!$G:$G,MATCH($A$4&D2, 'Training Records'!$A:$A&'Training Records'!$C:$C,0))," ")

 

[truebluewoman]

Try not using the entire column and instead name the data range and reference the range instead.

 

[Miller83]

Try not using the entire column and instead name the data range and reference the range instead.

Thanks the problem with that is data range is constantly changing as more people are hired

 

[Michael M]

data range is constantly changing

Thats okay but you don't need to search over a million cells do you......try setting the ranges to an arbitary amount, say 50000

=IFNA(INDEX('Training Records'!$G2:$G50000,MATCH($A$4&D2, 'Training Records'!$A2:$A50000&'Training Records'!$C2:$C50000,0)),"")

 

[truebluewoman]

Thanks the problem with that is data range is constantly changing as more people are hired

You can convert the information into a table as well. Tables automatically expand when you add rows of data. So will named ranges, so long as you use insert row within the range itself.