HELP WITH COMBINING LARGE FORMULAS

[Deleted]

Hello gurus!  I have attached my examples for you to look at to help my questions make sense.  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.

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

Thanks for reading and for your helpful ideas!

Ronna M.

[#2]

Hi

First the ResultsTable is referring to another file Practice Data 2.xlsx which you didn't provide - thus resulting the formulas in #REF errors.

And second what exactly are you trying to do? What is the desired output?

[#3]

Hi!  Thanks for your response.  Please find Practice Data 2.xlsx attached.

What I am trying to do is combine the two different formulas into one large formula.  I am trying to find out which candidate has the most job matches.

I hope this helps!

Ronna

[#4]

Could you provide a sheet with the desired output (just the values)?

Is Power Query available?

[#5]

Hi!  I've attached another workbook with the information I'm looking for on the green tab.  And no, I do not have Power Query.

Thanks!

Ronna

[#6]

Oh, too bad because I think PQ would be the best way to tackle the problem.

[#7]

Hmm.  Could you please send me the PQ information anyway?  My co-worker has used that before (I think) and he might be able to explain it to me.

Thanks!

Ronna

[#8]

That will take some time. First I have to understand your model.