Index Match based on multiple variables

[Gift2women]

Okay, after reading through a number of posts, I could not find an answer to my question, so if this is already posted elsewhere, i apologize.
What I am attempting to do is run the following formula, without it having to calculate. (I know I can set the calculation to manual and all that jazz, but I will not be the primary person using this spreadsheet, and that means that I cannot be sure that they would do it properly.):
{=INDEX(Sheet3!D:D,MATCH(M1,IF(Sheet3!B:B=N1,Sheet3!A:A),0))}

Sheet1
A                 M              N
Forumula       Pacific       Su 10a-6p, Mo 10a-6p, Tu 10a-6p

Sheet3
A           B                                                D
Eastern   Su 10a-6p, Mo 10a-6p, Tu 10a-6p    E Daily 10-6
Pacific    Su 10a-6p, Mo 1p-6p, Tu 10a-6p      P aS/TW 10-6, M 1-6, hF Cx
Pacific    Su 10a-6p, Mo 10a-6p, Tu 10a-6p    P Daily 10-6
Pacific    Su 9a-5p, Mo 9a-5p, Tu 9a-5p         P Daily 9-5
Central   Su 10a-6p, Mo 10a-6p, Tu 10a-6p    C Daily 10-6


As Sheet1!M=Sheet3!A AND Sheet1!N=Sheet3!B WHERE Sheet3! D="P Daily 10-6"; I want my formula to return "P Daily 10-6". {=INDEX(Sheet3!D:D,MATCH(M1,IF(Sheet3!B:B=N1,Sheet3!A:A),0))} does this, but it takes way too long on my 3,223 records that it is doing this on.

Windows XP/Excel 2007E

 

[T. Valko]

Okay, after reading through a number of posts, I could not find an answer to my question, so if this is already posted elsewhere, i apologize.
What I am attempting to do is run the following formula, without it having to calculate. (I know I can set the calculation to manual and all that jazz, but I will not be the primary person using this spreadsheet, and that means that I cannot be sure that they would do it properly.):
{=INDEX(Sheet3!D:D,MATCH(M1,IF(Sheet3!B:B=N1,Sheet3!A:A),0))}

Sheet1
A M N
Forumula Pacific Su 10a-6p, Mo 10a-6p, Tu 10a-6p

Sheet3
A B D
Eastern Su 10a-6p, Mo 10a-6p, Tu 10a-6p E Daily 10-6
Pacific Su 10a-6p, Mo 1p-6p, Tu 10a-6p P aS/TW 10-6, M 1-6, hF Cx
Pacific Su 10a-6p, Mo 10a-6p, Tu 10a-6p P Daily 10-6
Pacific Su 9a-5p, Mo 9a-5p, Tu 9a-5p P Daily 9-5
Central Su 10a-6p, Mo 10a-6p, Tu 10a-6p C Daily 10-6


As Sheet1!M=Sheet3!A AND Sheet1!N=Sheet3!B WHERE Sheet3! D="P Daily 10-6"; I want my formula to return "P Daily 10-6". {=INDEX(Sheet3!D:D,MATCH(M1,IF(Sheet3!B:B=N1,Sheet3!A:A),0))} does this, but it takes way too long on my 3,223 records that it is doing this on.

Windows XP/Excel 2007E

Don't use entire columns as range references. Use smaller specific ranges.

Array entered:

=INDEX(Sheet3!D:D,MATCH(M1,IF(Sheet3!B1:B3250=N1,Sheet3!A1:A3250),0))

** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.

 

[Gift2women]

So, does this mean that there is no simpler (non Ctrl+Shft+Ent) method to do what I'm attempting to do?

 

[T. Valko]

So, does this mean that there is no simpler (non Ctrl+Shft+Ent) method to do what I'm attempting to do?

Well, what you're currently doing is not complicated but you shouldn't be testing the entire column if you're only using ~3500 rows.

If you're using Excel 2007 and your data runs down to about 3500 rows, testing the entire column you're wasting calculation resources because you're testing 1,045,076 empty cells.