"If Match" formula

Options

Need help with an "If Match" formula to search for if a cell at row is on a list and return a check to another column

Scotty Andersen

Director of Engineering

Office 813-926-2958

Cell 801-589-3772

Direct Line 385-233-4122

Answers

  • Kaveri Vipat
    Kaveri Vipat ✭✭✭✭✭✭
    Options

    Hi Scotty Andersen,

    You can try this formula as per your condition:

    The list column is used as a reference to check whether the color column's cell falls within that range. If it does, the box will be checked; otherwise, it will remain unchecked.

    Hope it will work for you.

    Thanks,

    Kaveri Vipat

    Senior Associate - Smartsheet Development, Ignatiuz Software

    2023 Core Product Certified

    Did this answer help you? Show some love by marking this answer as "Insightful💡" or "Awesome❤️" and "Vote Up⬆️"

  • Jason Albrecht
    Jason Albrecht ✭✭✭✭✭✭
    Options

    Hi @Scotty Andersen

    Something like this in the check column then?

    =IF ( INDEX ( list range , MATCH ( cell@row , list range , 0 ), list check column number ) = cell@row , 1 , 0 )

    Hope this helps and that you have a great day,

    Jason Albrecht MBA, MBus(AppFin), DipFinMgt

    LinkedIn profile - Open to work

  • Scotty Andersen
    Options

    I tried so I have a list of numbers that is my row has that number i need the box to be checked

    Scotty Andersen

    Director of Engineering

    Office 813-926-2958

    Cell 801-589-3772

    Direct Line 385-233-4122

  • Jason Albrecht
    Jason Albrecht ✭✭✭✭✭✭
    Options

    Sorry @Scotty Andersen,

    I must be missing something. May I ask for your last comment to be rephrased? I'm not sure if you're making a comment or asking a question, or both!?

    If you'd like some more help, please provide a screenshot of your work (redacting sensitive information), so we can better understand what you're after.

    Hope this helps and that you have a great day,

    Jason Albrecht MBA, MBus(AppFin), DipFinMgt

    LinkedIn profile - Open to work

  • Scotty Andersen
    Options

    here is the formula so the list of poles on the list are on a separate sheet. =IF(MATCH([MPU Pole #]@row, {Sheet- Manitowoc Rejected Poles Range 1}), 1, 0)

    Scotty Andersen

    Director of Engineering

    Office 813-926-2958

    Cell 801-589-3772

    Direct Line 385-233-4122

  • Jason Albrecht
    Jason Albrecht ✭✭✭✭✭✭
    Options

    Hello @Scotty Andersen

    Did you end up finding a solution?

    Looking at your formula I note that the MATCH function "Returns the relative position of a value in a range (lookup table). The first position is 1." The function also has an optional search_type, where I assume the data in the other sheet is not sorted?

    So, may I recommend the following?

    =IFERROR(IF(MATCH([MPU Pole #]@row, {Sheet- Manitowoc Rejected Poles Range 1}, 0) > 0, 1, 0), 0)

    The formula says:

    • If there is a match (where the position number returned is greater than 0), return "1"
    • If there is no match, or if there is an error, return "0"


    Hope this helps and that you have a great day,

    Jason Albrecht MBA, MBus(AppFin), DipFinMgt

    LinkedIn profile - Open to work

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!