Nested IF and MATCH statement issue

[deckerch]

I am trying to create a project rollup cell based on the values of the values of the subtasks under the main task. The formula I'm using is:

=IF(ISTEXT(MATCH("Need Help",$F3:$F7)),"Need Help",IF(MATCH("Some Issues",$F3:$F7),"Some Issues",IF(MATCH("On Track",$F3:$F7),"WTF?")))

My data set contains all three responses. The result I'm getting is "Some Issues". I would have expected that the "Need Help" would solve first and that would be the answer. Any suggestions?

Thanks in advance for any assistance!

 

[Special-K99]

Your MATCH syntax is incorrect.
What result are you expecting if it finds of those phrases?

 

[Kuljack]

I am trying to create a project rollup cell based on the values of the values of the subtasks under the main task. The formula I'm using is:

=IF(ISTEXT(MATCH("Need Help",$F3:$F7)),"Need Help",IF(MATCH("Some Issues",$F3:$F7),"Some Issues",IF(MATCH("On Track",$F3:$F7),"WTF?")))

My data set contains all three responses. The result I'm getting is "Some Issues". I would have expected that the "Need Help" would solve first and that would be the answer. Any suggestions?

Thanks in advance for any assistance!

Match function returns a number, try isnumber:

=IF(ISNUMBER(MATCH("Need Help",$F3:$F7)),"Need Help",IF(ISNUMBER(MATCH("Some Issues",$F3:$F7)),"Some Issues",IF(ISNUMBER(MATCH("On Track",$F3:$F7)),"On Track","WTF?")))

However, this is going to come back with the first match it finds, it won't look for "Some Issues" or "On Track" if it finds Need Help.

 

[deckerch]

Your MATCH syntax is incorrect.
What result are you expecting if it finds of those phrases?

I expect it resolve in the order the arguments are presented; i.e. If it finds "Need Help", that would be the answer, if it finds "Some Issues" then that would be the answer, and if it finds "On Track" that would be the answer.

 

[deckerch]

Match function returns a number, try isnumber:

=IF(ISNUMBER(MATCH("Need Help",$F3:$F7)),"Need Help",IF(ISNUMBER(MATCH("Some Issues",$F3:$F7)),"Some Issues",IF(ISNUMBER(MATCH("On Track",$F3:$F7)),"On Track","WTF?")))

However, this is going to come back with the first match it finds, it won't look for "Some Issues" or "On Track" if it finds Need Help.

It worked! Fantastic! Thank you, Thank you, and Thank you!!!!

 

[Kuljack]

I expect it resolve in the order the arguments are presented; i.e. If it finds "Need Help", that would be the answer, if it finds "Some Issues" then that would be the answer, and if it finds "On Track" that would be the answer.

I think what Special was saying is that you had a few breaks in your code:

=IF(ISTEXT(MATCH("Need Help",$F3:$F7)),"Need Help",IF(MATCH("Some Issues",$F3:$F7),"Some Issues",IF(MATCH("On Track",$F3:$F7),"WTF?")))

You only called on the "istext" once, when you have three arguements written, and your did not place the "true" statement on the last arguement for "on track". The syntax being off because all three arguements should mirror one another.


Glad the suggestion worked, best of luck!

 

[deckerch]

I think what Special was saying is that you had a few breaks in your code:

=IF(ISTEXT(MATCH("Need Help",$F3:$F7)),"Need Help",IF(MATCH("Some Issues",$F3:$F7),"Some Issues",IF(MATCH("On Track",$F3:$F7),"WTF?")))

You only called on the "istext" once, when you have three arguements written, and your did not place the "true" statement on the last arguement for "on track". The syntax being off because all three arguements should mirror one another.


Glad the suggestion worked, best of luck!

Ugh! I thought it was working, but during my testing I'm finding that it's not working consistently. Here's what I ended up with my final formula:

=IF(ISNUMBER(MATCH("Need Help",$F3:$F7)),"Need Help",IF(ISNUMBER(MATCH("Some Issues",$F3:$F7)),"Some Issues",IF(ISNUMBER(MATCH("On Track",$F3:$F7)),"On Track", "No Match")))

 

[Kuljack]

What are your expected results, and how is the inconsistency looking, what are you getting vs what you want?

 

[deckerch]

What are your expected results, and how is the inconsistency looking, what are you getting vs what you want?

For example, if I fill in the cells with all "Completed", the formula is returning "Need Help". If I input all "On Track", it returns "Some Issues".

 

[Kuljack]

For example, if I fill in the cells with all "Completed", the formula is returning "Need Help". If I input all "On Track", it returns "Some Issues".

That's on me. Forgot the match style deliminator, so it was default to "1" which is a similiar match. Try this:

=IF(ISNUMBER(MATCH("Need Help",$F3:$F7,0)),"Need Help",IF(ISNUMBER(MATCH("Some Issues",$F3:$F7,0)),"Some Issues",IF(ISNUMBER(MATCH("On Track",$F3:$F7,0)),"On Track", "No Match")))