Skip to content
  • Try for free

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

    Match Formula Help

    ✭✭✭
    in Formulas and Functions

    I have created two sheets and in need of some help with a formula.

    Sheet 1: Vendor Directory (with columns "Vendor ID", "Vendor Pin", and "Vendor Verified").

    Sheet 2: Vendor Verification (Which vendors will populate with their "Vendor ID" and "Vendor Pin")

    When a row is entered in Sheet 2 the formula should look at sheet 1 to see if it matches with any "Vendor ID" and "Vendor Pin". If it does match, I need it noted as "Yes" under "Vendor Verified" on sheet 1.

    Thank you in advance for your help!

    Tags:
    · Share on FacebookShare on Twitter

    Best Answer

    • Community Champion
      Answer ✓

      Hi @Natalie11

      In Sheet 2, have a column to combine the entered Vendor ID and Vendor Pin with the formula =[Vendor ID]@row + " " + [Vendor Pin]@row. Once done, in Sheet 1's Vendor Verified column have the formula =IF(INDEX(COLLECT({column reference of the new combination column of Vendor ID and Vendor Pin}, {Column reference of Vendor ID in Sheet 2}, [Vendor ID]@row), 1)=[Vendor ID]@row + " " + [Vendor Pin]@row, "Yes", "No")

      Thanks,

      Aravind GP| Delivery Manager

      Atturra Data & Integration

      M: +61493337445

      E:Aravind.GP@atturra.com

      W: www.atturra.com

      · Share on FacebookShare on Twitter

    Answers

    • Community Champion
      Answer ✓

      Hi @Natalie11

      In Sheet 2, have a column to combine the entered Vendor ID and Vendor Pin with the formula =[Vendor ID]@row + " " + [Vendor Pin]@row. Once done, in Sheet 1's Vendor Verified column have the formula =IF(INDEX(COLLECT({column reference of the new combination column of Vendor ID and Vendor Pin}, {Column reference of Vendor ID in Sheet 2}, [Vendor ID]@row), 1)=[Vendor ID]@row + " " + [Vendor Pin]@row, "Yes", "No")

      Thanks,

      Aravind GP| Delivery Manager

      Atturra Data & Integration

      M: +61493337445

      E:Aravind.GP@atturra.com

      W: www.atturra.com

      · Share on FacebookShare on Twitter
    • ✭✭✭

      @AravindGP Thank you so much!⭐️

      · Share on FacebookShare on Twitter

    Sign in
    Try for free

    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