INDEX/MATCH array REALLY slow

[cherryduck]

Hi there, I'm using an INDEX/MATCH array to look up a date and name in one sheet and return the number of hours worked into another.

The E column in the Overtime sheet contains the name, the H column in the Overtime sheet contains the date. B3 holds the month in name format (April for example) and F6, G6 etc (ie row 6) contains the current day in numerical value (01 for 1st, 02 for 2nd etc).

I've gotten this working with the following formula:

=IFERROR(INDEX(Table_owssvr_1[#All],MATCH(1,(Overtime!$E:$E=$B7)*(TEXT(Overtime!$H:$H,"mmmm")=$B$3)*(TEXT(Overtime!$H:$H,"DD")=F$6),0),10),"")

In the above case, B7 is where our name is, on the next line down we'll be looking at the name in B8 and so on. The 10th column in the Overtime sheet holds the number of hours worked.

It works, however it is SLOW. With a small test data set of 5 values in the overtime sheet, it took roughly 10 minutes to update

Can anyone suggest how I could speed this up? I'm using Excel 2016 on a Windows 7 laptop.

 

[AlanY]

because you use the whole column, $H:$H etc as range that is over 1m cells each.
try to limit the ranges just larger than needed, e.g. $H$1:$H$1000 etc

 

[steve the fish]

Hi. You need to use a more appropriate range. Using full column references means excel has to do a million calculations even if cells are empty. ie dont use A:A use A1:A100

 

[cherryduck]

Perfect, thanks both! From 10 minutes to 5 seconds

 

[AlanY]

you're welcome