Complex lookup functions causing slowdown

[rinkjames]

Hi

I'm using index and match functions to lookup data and I'm also using them for conditional formatting but this seems to be causing irritating calculation slowdowns. I'm happy with the formula in column E, but it's the formula for column D that seems to slow everything down. Essentially, the formula in column E of the first worksheet looks to the value of A3 and then lists corresponding data from the second worksheet. The formula in column D of the first worksheet looks to the "requirement" column of the data in the second worksheet and returns "true" or "false" based on whether the values in columns B and C of the first worksheet meet those requirements. I intended to use this latter formula as a conditional-formatting rule for the cells in column D, but I don't want to go any further without fixing the slowdown (if I can). I've attached the workbook in case anyone's willing to try to make it more efficient. It's for a "final fantasy" video game by the way.

Jobs.xlsx

[BB1972]

rinkjames,

If I'm not mistaken, Excel 2011 for Mac has over 1 million rows per sheet. So go easy on the D:D, C:C etc column references - if you use a range, ie D1:D1000 (or whatever is appropriate), that should help to speed things up a little.

[rinkjames]

rinkjames,

If I'm not mistaken, Excel 2011 for Mac has over 1 million rows per sheet. So go easy on the D:D, C:C etc column references - if you use a range, ie D1:D1000 (or whatever is appropriate), that should help to speed things up a little.

Oh my word, I feel like such an idiot now. Haha thanks.

[BB1972]

No problem. Did you try it? Did it help?

[rinkjames]

No problem. Did you try it? Did it help?

Yes it did, it's practically instantaneous now.

[BB1972]

Great - glad that worked out.