Forum Discussion

Anonymous's avatar
Anonymous
Sep 05, 2018

MULTI-DATA CELLS - WHAT FORMULA DO I USE?

Hello again, wonderful Excel gurus!  I need help with writing a formula that extracts (if possible) information from cells that contain multiple data.  I have included my examples for you to look at to make my explanation clearer.

 

I figured out how to write the formula when there is a direct 1:1 match.  When I changed part of the data to make it multi-data cells, I now can't figure out what to do.

 

Summary: I am looking for a match of contract = contract.

Example 1: (I got this part to work)

EmpPref = contract & EmpType = contract = MATCH

 

Example2: (I can't figure out the formula for this)

EmpPref = contract,any,remote & EmpType = contract = MATCH 

 

Any help is appreciated!

 

Thanks!

Ronna

 

excel
Formulas and Functions

7 Replies

    • Anonymous's avatar
      Anonymous
      Sep 06, 2018

      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

      • SergeiBaklan's avatar
        SergeiBaklan
        Diamond Contributor
        Sep 06, 2018

        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.

Resources