# Index Match using multiple criteria to match to

1. ## Index Match using multiple criteria to match to

Hello,

I need to create a consolidated table for the current date that matches the PC on one sheet and lists all the jobs worked for that PC for today's date.

The date in the Man Hours sheet starts on Row 5 and ends on Row 900 in Column A and needs to match the date on the SOCAL Daily Site Analysis sheet Cell C4
The PC in the Man Hours sheet starts on Row 5 and ends on Row 900 in Column D and needs to match the PC on the SOCAL Daily Site Analysis sheet Cell B3
The end result would be a list of sites (A consolidated list, no duplicates) from the Man Hours sheet (Column I, Rows 5 thru 900I to populate the SOCAL Daily Site Analysis sheet

I tried piecing the following formula together and I am not getting any positive results.

{=IFERROR(INDEX('Man Hours'!I\$5:I\$900,MATCH(IF('Man Hours'!\$D\$5:\$D\$900=322001,AND('Man Hours'!\$A\$5:\$A\$900=\$C\$4,ROW('Man Hours'!I\$5:I\$900))),""}

Any help would be greatly appreciated.

Signed,

Formulatically Challenged

2. ## Re: Index Match using multiple criteria to match to

Hi,

Please post a sample template of your workbook using "Go Advanced" option.

3. ## Re: Index Match using multiple criteria to match to

I am trying to attach a sample workbook and I am getting a undefined permissions error. Am I doing something wrong or is there a special setting on my profile I need to change.

4. ## Re: Index Match using multiple criteria to match to

You said that equation doesn't get "any positive results". What exactly does that mean? What do you get when you use that formula? An error? Nothing?

5. ## Re: Index Match using multiple criteria to match to

It just displays the entire formula as a text entry. I am still unable to attach the workbook template.

6. ## Re: Index Match using multiple criteria to match to

Delete the array brackets at the beginning and end. To get an array, just type the regular formula and instead of hitting enter when you're done, hit ctrl+shift+enter. Then the array brackets will appear. Well, after you get the error saying you're missing a parenthesis.

7. ## Re: Index Match using multiple criteria to match to

Maybe this...

Array entered**:

=IFERROR(INDEX('Man Hours'!I\$5:I\$900,MATCH(1,('Man Hours'!\$D\$5:\$D\$900=322001)*('Man Hours'!\$A\$5:\$A\$900=\$C\$4),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.

8. ## Re: Index Match using multiple criteria to match to

I am getting a blank entry.

9. ## Re: Index Match using multiple criteria to match to

Then no data meet your requirement.

10. ## Re: Index Match using multiple criteria to match to

Originally Posted by sacastiglia
I am getting a blank entry.
Did you enter the formula as an array formula?

Array formulas are entered differently than a regular formula. After you type in a regular formula you hit the ENTER key. With an array formula you must use a combination of keys. Those keys are the CTRL key, the SHIFT key and the ENTER key. That is, hold down both the CTRL key and the SHIFT key then hit the ENTER key.

When done properly Excel will enclose the formula in squiggly brackets { }. You can't just type these brackets in, you must use the key combo to produce them. Also, anytime you edit an array formula it must be re-entered as an array using the key combo.

If you did array enter the formula a blank means that there was not a match of one or both criteria.

#### Thread Information

##### Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1