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

Jennifer Lenander

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?

Mark Cronk

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

Mark Cronk

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

Brett Robinson

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

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

Happy to help. Thanks for using the community.

Mark