Forum Discussion
MULTI-DATA CELLS - WHAT FORMULA DO I USE?
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
Sergei, I'm closing in on completing my Excel project. What I need help with is that now I have two separate formulas that work for different parts of my overall Excel project. One formula is for static information and the other formula is for dynamic data (or multi-data cells). I want to combine the two formulas to ultimately return the information on the green "Combined Info" tab...the Candidate ID, #ComboMatch (the # of Job Order matches) and JobOrderIDs. I have attached my examples for you to look at to help my questions make sense.
The two working formulas I have are:
Static formula: =COUNTIFS(JOs[Dept],[@Dept],JOs[Relo],[@Relo],JOs[2DigZip],[@2DigZip])+(0.00001*ROW())
Dynamic formula: =(IF((ISNUMBER(SEARCH("in play",'Practice Data 2.xlsx'!CandTable[@Status]))+ISNUMBER(SEARCH("ready",'Practice Data 2.xlsx'!CandTable[@Status])))*('Practice Data 2.xlsx'!JOtable[@Status]="in play"),"1","0"))*(IF((ISNUMBER(SEARCH("contract",'Practice Data 2.xlsx'!CandTable[@EmpPref]))+ISNUMBER(SEARCH("perm",'Practice Data 2.xlsx'!CandTable[@EmpPref])))*('Practice Data 2.xlsx'!JOtable[@EmpPref]="contract"),"1","0"))
How do I combine these two formulas into one large one? I am not sure which is more useful, TEXTJOIN or IF(ISNUMBER(SEARCH....)) or if there is something else I am missing.
Thanks for reading and for your helpful ideas!
Ronna M.