# Thread: Use MATCH or INDEX? (Excel 2003)

1. ## Use MATCH or INDEX? (Excel 2003)

I'm never certain which of these to use...I have a list of locations; people are in various locations; there are 3 types of jobs being done....I want to calculate how many of each job type is being done in each location (see sample)..........I tried to use MATCH and a LOOKUP but it always returns #NAME...??...any ideas? I read about an ARRAY formula but didn't try it b/c it seemed that it calculates slowly ??..??

2. ## Re: Use MATCH or INDEX? (Excel 2003)

I would probably use a pivot table for that, but the formula you need is:
=SUMPRODUCT((\$C\$5:\$C\$14=\$H5)*(\$D\$5:\$D\$14=I\$4))
in I5, then copy across and down. (see attached for both approaches)

3. ## Re: Use MATCH or INDEX? (Excel 2003)

Thanks, Rory.....for the pivot table...1st one I'e seen that I think I can understand....can I use the formula you gave me if the source data is on a different wksheet from the target data/formula?

EDIT: Thank you, Rory (and bosco-yip)...I got it all working...now I need to learn about pivot tables..

4. ## Re: Use MATCH or INDEX? (Excel 2003)

Try………

Cell I5, entered the formula and copied across and down

=SUMPRODUCT((\$C\$5:\$C\$14=\$H5)*(\$D\$5:\$D\$14=I\$4))

or

=SUMPRODUCT(--(\$C\$5:\$C\$14&\$D\$5:\$D\$14=\$H5&I\$4))

Regards

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•