IF(AND((VLOOKUP, convert to IF(AND((Index(Match

I am using the below vlookup but my sheet getting too big and I need to convert to an Index(Match).

This works: =IF(AND((VLOOKUP(Code@row, {Master List (US)}, 13, false) = "Mandatory Training Non-Compliance"), (VLOOKUP(Code@row, {Master List (US)}, 16, false) = "2020 Q2")), (VLOOKUP(Code@row, {Master List (US)}, 15, false)))

I think I'm close. I'm pretty sure I have a parentheses in the wrong place but I've tried a ton of different things and I can't get it to work.

=IF(AND((INDEX({Reason}, MATCH(Code@row, {DSP Code}, 0)) = "Mandatory Training Non-Compliance"), (INDEX({Training Failure}, MATCH(Code@row, {DSP Code}, 0)) = [Failure Period]@row)), INDEX({Re-Enrolled}, MATCH(Code@row, {DSP Code})), 0)


Anyone have any thoughts on where I'm going wrong?

Best Answer

  • Mark Cronk
    Mark Cronk ✭✭✭✭✭✭
    edited 12/30/20 Answer ✓

    Hi @Jennifer Lenander ,

    Kind of shooting in the dark here, but I made a couple modifications to your formula.

    =IF(AND(INDEX({Reason}, MATCH(Code@row, {DSP Code}, 0)) = "Mandatory Training Non-Compliance", INDEX({Training Failure}, MATCH(Code@row, {DSP Code}, 0)) = [Failure Period]@row), INDEX({Re-Enrolled}, MATCH(Code@row, {DSP Code},0)), 0)

    Work?

    Mark


    I'm grateful for your "Vote Up" or "Insightful". Thank you for contributing to the Community.

Answers

  • Mark Cronk
    Mark Cronk ✭✭✭✭✭✭
    edited 12/30/20 Answer ✓

    Hi @Jennifer Lenander ,

    Kind of shooting in the dark here, but I made a couple modifications to your formula.

    =IF(AND(INDEX({Reason}, MATCH(Code@row, {DSP Code}, 0)) = "Mandatory Training Non-Compliance", INDEX({Training Failure}, MATCH(Code@row, {DSP Code}, 0)) = [Failure Period]@row), INDEX({Re-Enrolled}, MATCH(Code@row, {DSP Code},0)), 0)

    Work?

    Mark


    I'm grateful for your "Vote Up" or "Insightful". Thank you for contributing to the Community.

  • Without being able to handle the formula in the sheet, it's kinda tough but I think there are some extra "(" ")" and the last match didn't have ",0" in the right spot. Try this:

    =IF(AND(INDEX({Reason}, MATCH(Code@row, {DSP Code}, 0)) = "Mandatory Training Non-Compliance", INDEX({Training Failure}, MATCH(Code@row, {DSP Code}, 0)) = [Failure Period]@row), INDEX({Re-Enrolled}, MATCH(Code@row, {DSP Code}, 0)))

    You may get a #NoMatch if the two criteria are not met. If so, you can test the function by adding a return value to the if/then from when it's false. You do that before the last ")" by adding a comma then the value you want it to enter if the criteria are false.

    If that doesn't work, start by breaking it into parts. I'd do something like this:

    First: =if(INDEX({Reason}, MATCH(Code@row, {DSP Code}, 0)) = "Mandatory Training Non-Compliance", "Yes", "No')

    I'd use the Yes/No simply to see if the logic in the formula is working. Once I know that foundation works, I start adding to it. Second formula: =if(INDEX({Reason}, MATCH(Code@row, {DSP Code}, 0)) = "Mandatory Training Non-Compliance", INDEX({Re-Enrolled}, MATCH(Code@row, {DSP Code}, 0)), "No')

    If the second formula returns a value from the {Re-Enrolled} column when it finds "Mandatory Training Non-Compliance" or a "no" when it doesn't find that phrase in the Code column, then we know it's working and it's time to add the second criteria.

    To do this, I'd do a third formula as: =if(INDEX({Training Failure}, MATCH(Code@row, {DSP Code}, 0)) = [Failure Period]@row, "Yes", "No')

    This would let me know if the logic is working on the second part of the criteria. Once I know the logic works, I'd combined it with the And().

    Hope this helps

  • Jennifer Lenander
    Jennifer Lenander ✭✭✭✭✭

    Thanks Mark & Brett! My stupid issue was I was wanting to return a date and failed to make my column a date column. I really appreciate all your help!

  • Mark Cronk
    Mark Cronk ✭✭✭✭✭✭

    Happy to help. Thanks for using the community.

    Mark


    I'm grateful for your "Vote Up" or "Insightful". Thank you for contributing to the Community.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!