Forum Discussion
MULTI-DATA CELLS - WHAT FORMULA DO I USE?
Hi Ronna,
That could be
=IF(ISNUMBER(SEARCH("contract",[@EmpPref2]))*([@EmpType2]="contract"),"MATCH","0")
and attached
Sergei, thank you very much! I didn't even think of using ISNUMBER and SEARCH so thanks for teaching me something new!
Now, I have a twist on the formula. In Example Table 3 (attached), I am trying to write a formula that says: IF column EmpPref3 says "contract" OR "perm" AND column EmpType says "perm", then return MATCH; otherwise return 0.
What do I need to add to your formula to make this work?
Thanks,
Ronna
Hi Ronna,
That will be
=IF((ISNUMBER(SEARCH("contract",[@EmpPref3]))+ISNUMBER(SEARCH("perm",[@EmpPref3]))),"MATCH","0")and attached. Take into account what zero eqs to FALSE and any other number eqs to TRUE in logical operations.
Sergei, thanks again for your help. I have one last step (I think) that I need help with for this formula and I've also included my sheet for you to look at to see what I'm trying to do.
In Table 3, I am trying to write a formula that says: IF column EmpPref3 says "contract" OR "perm" AND column EmpType3 says "perm", then return 1; otherwise return 0.
My guess at the formula is this: = IF (( ISNUMBER (SEARCH ("contract", [@EmpPref3])) + ISNUMBER (SEARCH ("perm", [@EmpPref3]))) * (( [@EmpType3] = "perm" ), "1", "0")
What am I missing or doing incorrectly? Any help or tips are appreciated!
Thank you!
Ronna
Ronna, you have correct formula in your post, but in the file formula your forgot to add AND condition
but shall be
=IF((ISNUMBER(SEARCH("contract",[@EmpPref3]))+ISNUMBER(SEARCH("perm",[@EmpPref3])))*([@EmpType3]="perm"),"1","0")and attached