Skip to content
  • Try Smartsheet

    • Formulas and Functions

    Formulas and Functions

    Ask for help with your formula and find examples of how others use formulas and functions to solve a similar problem.

    Index / Match / Join Formula across 2 sheets

    ✭✭
    in Formulas and Functions

    Hi,

    I am trying to create a formula to identify a contact name from one sheet (named "LS Store Dev Contacts List"), based on data from two columns ("Business Division" and "Project Type") from a second sheet (named "Test Fit Tracker"). So for example, if on the "Test Fit Tracker", "Regional" (Biz Division) and "New" (Project Type) are identified on a row, the formula will input the correct Contact person associated with that combination from the LS Store Dev Contacts List.

    I am currently getting #Unparsable with this formula:

    =INDEX({LS Store Dev Contacts List | Business Division:{LS Store Dev Contacts {LS Store Dev Contacts List | Project Types}, MATCH(JOIN([Business Division]@row:[Project Type]@row, " , "), {LS Store Dev Contacts List | Biz Div and Proj Type:{LS Store Dev Contacts List | Biz Div and Proj Type},1){LS Store Dev Contacts List | Contact Name})}))

    Here is the Contact List:

    image.png

    Any help would be greatly appreciated.

    Thanks, Mark

    · Share on FacebookShare on Twitter

    Sign in
    Try Smartsheet

    Answers

    • ✭✭

      If I understand your question correctly, I'd think you would want to try using a vlookup() instead. You want to return the value in Contact Name, based on the data in Business Division. Then you want to put that into an If statement, and do another VLOOKUP and pull the Contact Name if it matches on Project Types.

      https://app.smartsheet.com/sheets/5v9rRQ3hq9WPqqgP48ggxQrmR74jvR5R6rVFGWm1?view=grid

      · Share on FacebookShare on Twitter
    • ✭✭

      Problem with that is some business units have multiple different people, and that is why it must cross reference both the biz dev and project type as the same time.

      · Share on FacebookShare on Twitter
    • Community Champion

      So there are 2 ways I know to get this result. The first way is similar to what you seem to have tried.


      Index({Return},match(Join([L 1]@row:[L 2]@row),{search},0)

      Where you use a helper column in the search sheet to join multiple columns together then search by the joined values.


      The second way, and way that I am more prone to use to search, is index(collect

      =index(collect({return range},{first criteria},Criteria1@row,{second criteria},Criteria2@row),1)

      · Share on FacebookShare on Twitter

    Sign in
    Try Smartsheet

    Help Article Resources

    Create and edit formulas in Smartsheet

    Formula combinations for cross sheet references

    Smartsheet functions list

    Quick Links

    Categories

    Want to practice working with formulas directly in Smartsheet?

    Check out the Formula Handbook template!

    Trending in Formulas and Functions