Return a value based on multiple criteria

Options
GM185220
GM185220
edited 05/16/24 in Formulas and Functions

Hi Formula gurus

I am looking for formula which returns a value from a single column based on multiple criteria. So from one sheet I have a part number column a Order no column and I want to return the purchase order no from another sheet which has the fields part number and order no fields in it too for the match.

Hoping someone can help :-) #uselessatformulas

Answers

  • Mark.poole
    Mark.poole ✭✭✭✭✭
    edited 05/16/24
    Options

    Hi @GM185220

    See if this helps you out. IF not let me know and I should be able to help.

    =Index(Collect({Purchase Order Ref},{Part Number Ref},[Part Number]@row,{Order Number Ref},[Order Number]@row),0)

    If you found this comment helpful. Please respond with any of the buttons below. Awesome🖤, Insightful💡, Upvote⬆️, or accepted answer. Not only will this help others searching for the same answer, but help me as well. Thank you.

  • GM185220
    Options

    @Mark Poole - Can I use an index match I am more familiar with that?

  • Mark.poole
    Mark.poole ✭✭✭✭✭
    edited 05/17/24
    Options

    @GM185220 index match only lets you use one criteria. Thus index(collect()). Just copy that formula and replace the part where is said… ref with what ever your references are. Personally I think index collect is more strait forward then index match. And your rows that correspond with where I put at row. If I knew your actual ref names and your actual column names I could just build the whole formula for you

    If you found this comment helpful. Please respond with any of the buttons below. Awesome🖤, Insightful💡, Upvote⬆️, or accepted answer. Not only will this help others searching for the same answer, but help me as well. Thank you.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!