If formula evaluating wrong statement

Alexandra Stewart

Hi Everyone,

I was hoping I could avoid Support for this one - not sure what I'm getting wrong here.

I have an if statement, if a certain cell states "no" then I want it to return blank, otherwise I want it to evaluate an index-match formula.

What I'm running into is that even when the cell is equal to no, it's still running the index-match formula and turns up a partial match.

I have a testing column with an if statement that again checks if the cell states "no" then it returns blank, otherwise, it will return a "yes". This formula is working perfectly fine.

Does anyone know what is going wrong here?

EE Delegation Period Formula: =IF([EE Delegation Active?]@row = "No", "", (INDEX([EE Delegation Key]:[EE Delegation End], MATCH("Active" + [Employee Email]@row, [EE Delegation Key]:[EE Delegation Key], 0), 67))) + "-" + (INDEX([EE Delegation Key]:[EE Delegation End], MATCH("Active" + [Employee Email]@row, [EE Delegation Key]:[EE Delegation Key], 0), 68))

testing formula: =IF([EE Delegation Active?]@row = "No", "", "Yes")

Jeff Reisman

@Alexandra Stewart Your formula is working perfectly, just not doing what you intended. When it finds a status of "No", it's running the "blank", and then concatenating the blank onto the front of the next formula you list. This is because Everything in BOLD below is not part of the IF Statement.

=IF([EE Delegation Active?]@row = "No", "", (INDEX([EE Delegation Key]:[EE Delegation End], MATCH("Active" + [Employee Email]@row, [EE Delegation Key]:[EE Delegation Key], 0), 67))) + "-" + (INDEX([EE Delegation Key]:[EE Delegation End], MATCH("Active" + [Employee Email]@row, [EE Delegation Key]:[EE Delegation Key], 0), 68))

So what you need to do is wrap everything after "No", "", in parentheses and close out the IF at the very end. This makes the entire "INDEX + "-" + INDEX" into the false condition of the IF. You'll know it's right if the very last end parentheses is the same color as the very first open parentheses (the one right after =IF).

Jeff Reisman

@Alexandra Stewart Your formula is working perfectly, just not doing what you intended. When it finds a status of "No", it's running the "blank", and then concatenating the blank onto the front of the next formula you list. This is because Everything in BOLD below is not part of the IF Statement.

=IF([EE Delegation Active?]@row = "No", "", (INDEX([EE Delegation Key]:[EE Delegation End], MATCH("Active" + [Employee Email]@row, [EE Delegation Key]:[EE Delegation Key], 0), 67))) + "-" + (INDEX([EE Delegation Key]:[EE Delegation End], MATCH("Active" + [Employee Email]@row, [EE Delegation Key]:[EE Delegation Key], 0), 68))

So what you need to do is wrap everything after "No", "", in parentheses and close out the IF at the very end. This makes the entire "INDEX + "-" + INDEX" into the false condition of the IF. You'll know it's right if the very last end parentheses is the same color as the very first open parentheses (the one right after =IF).

Alexandra Stewart

Thank you so much for your help! I can't believe I didn't even catch that.

I hope you have a good rest of your day!