CODEWITHSUNDEEP
excelexcel-2010conditional-statementsworksheet-functionarray-formulas
MixedBeans
MixedBeans

Reputation: 159

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

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)}

Upvotes: 1

Views: 145

Answers (2)

pnuts
pnuts

Reputation: 59450

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)}

Upvotes: 0

rgo
rgo

Reputation: 491

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.

Upvotes: 0

Related Questions