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?

Find more posts tagged with

Accepted answers

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

All comments

Mark Cronk

Hi @Jennifer Lenander ,

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

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:

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

Sign in to join the conversation!

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, latest, sort, sortType, lookupSort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.