How do I add a third criteria to this Excel formula?

1
votes

Here is the formula I am using: 

{=INDEX(AA$1:AJ$1831,MATCH(1,(AA$1:AA$1831=C2)*(AD$1:AD$1831=O2),0),10)}

It finds a number based on matching a name and hour of day. I need to refine this to also match day of week. The problem I am running into is that the day of the week is part of a string. Either "Sat,Sun" or "Mon,Tue,Wed,Thu,Fri".

How do I add to the formula above? to make:

{=INDEX(AA$1:AJ$1831,MATCH(1,(AA$1:AA$1831=C2)*(AD$1:AD$1831=O2)*(SEARCH(P2,AC:AC)),0),10)}
2
excelexcel-2010conditional-statementsworksheet-functionarray-formulas

2 Answers

0
votes

Please try: 

{=INDEX(AA$1:AJ$1831,MATCH(1,(AA$1:AA$1831=C2)*(FIND(P2,AC$1:AC$1831)>0)*(AD$1:AD$1831=O2),0),10)}
0
votes

Since you are looking for a number why not use SUMIFS to return a single value from a column based on multiple criteria? Arrays are old school and the SUMIFS function can accomplish the same. Search criteria can take on wildcards or use formulas like Mid,left, or right to strip a partial value out of a string.

Give it a try and reply back if you are still having problems.