Formula

Options

Hi

I have a scenario like below

If Award Type is MP, then Vlook up buyer name

If Award Type is non MP and Approval required is tick, then vlookup buyer name

The formula that I am thinking as below, but i think it is wrong. Can help to show me the correct formula

=IF(AND([Approval]@row = "Yes", [Award Type]@row="Non MP")), OR([Award Type]]@row = "MP", VLOOKUP(Commodity@row, {BuyerRange 1}, 3, 0))

Best Answer

  • Mark.poole
    Mark.poole ✭✭✭✭✭✭
    Answer ✓
    Options

    @HZAR

    I apologize I had a typo in the formulas. I corrected them below

    =IF(OR(AND([Approval]@row = 1, [Award Type]@row="Non MP"), [Award Type]@row = "MP"), VLOOKUP(Commodity@row, {BuyerRange 1}, 3, 0))

    Or use

    =IF(OR(AND([Approval]@row = "Yes", [Award Type]@row="Non MP"), [Award Type]@row = "MP"), VLOOKUP(Commodity@row, {BuyerRange 1}, 3, 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.

Answers

  • Itai
    Itai ✭✭✭✭✭✭
    Options

    Hi,

    It seems that the Vlookup is the same either way right? What are the return values? can you send some screenshots please?

    Are there any other options that are different than MP/non MP?

    Thanks

    Itai Perez

    Reporting and Project Manager

    If you found my comment helpful any reaction, Insightful, Awsome etc... would be appreciated🙂

    https://www.linkedin.com/in/itai-perez/

  • Mark.poole
    Mark.poole ✭✭✭✭✭✭
    Options

    @HZAR

    check boxes are Boolean. meaning they are 1 or 0. To get your formula to work.

    =IF(OR(AND([Approval]@row = 1, [Award Type]@row="Non MP"), [Award Type]]@row = "MP"), VLOOKUP(Commodity@row, {BuyerRange 1}, 3, 0))

    If it is not a check box then you need the below formula

    =IF(OR(AND([Approval]@row = "Yes", [Award Type]@row="Non MP"), [Award Type]]@row = "MP"), VLOOKUP(Commodity@row, {BuyerRange 1}, 3, 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.

  • HZAR
    HZAR ✭✭✭
    Options

    Hi @Mark.poole

    the formula that you have told is giving me Incorrect argument error. Need your help to advise. Thanks

  • Mark.poole
    Mark.poole ✭✭✭✭✭✭
    Answer ✓
    Options

    @HZAR

    I apologize I had a typo in the formulas. I corrected them below

    =IF(OR(AND([Approval]@row = 1, [Award Type]@row="Non MP"), [Award Type]@row = "MP"), VLOOKUP(Commodity@row, {BuyerRange 1}, 3, 0))

    Or use

    =IF(OR(AND([Approval]@row = "Yes", [Award Type]@row="Non MP"), [Award Type]@row = "MP"), VLOOKUP(Commodity@row, {BuyerRange 1}, 3, 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.

  • HZAR
    HZAR ✭✭✭
    Options

    Hi @Mark.poole Thanks for the help to show the correct way.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!