Adding IF Contain to a V-lookup Formula

Hello, I can't get a formula to work... I have a VLOOKUP formula that is working correctly in this sheet under "Payroll/Benefits need" column, pulling from a reference sheet which is HR PROJECTS Range 2.

=VLOOKUP([Project ID#]@row, {HR PROJECTS Range 2}, 13, false)

What I need to add to this formula is... IF the cell contains the word "Benefits" or "Payroll" the result should equal 1, or else equals 2. The result from the other sheet could have other words involved.

Can you help?

Find more posts tagged with

Accepted answers

Scott Peters

@jamiefico - OK, this is an easy adjustment: When the source field is a multiselect, we use the HAS function instead of CONTAINS. In this example, I also adjusted the name from "Teams Needed" to "HR Function" to match your column naming:

=IF(OR(HAS("Benefits", INDEX({HR FUNCTION}, MATCH([Project ID#]@row, {PROJECT IDS}, 0))), (HAS("Payroll", INDEX({HR FUNCTION}, MATCH([Project ID#]@row, {PROJECT IDS}, 0))))), 1, 2)

Something I breezed past in my first reply: I am aligned with many here in the SS Community that Index/Match is superior to vlookup, because index/match does not care if the columns in your source sheet get moved, renamed, or nearby columns are hidden.

All comments

Scott Peters

Hello @jamiefico - This would be a good use of Index/Match combined with Contains. Others might advise you with an Index/Collect formula. Without seeing your reference sheet, let's assume that Project IDs are in one column, and the 13th column is something like "Teams Needed"

=IF(OR(CONTAINS("Benefits", INDEX({TEAMS NEEDED}, MATCH([Project ID#]@row, {PROJECT IDS}, 0))), (CONTAINS("Payroll", INDEX({TEAMS NEEDED}, MATCH([Project ID#]@row, {PROJECT IDS}, 0))))), 1, 2)

This works if the Teams Needed column in your reference sheet is a text field, and these phrases are embedded in that text. If it is a multi-select column, you would switch this from using Contains to using HAS instead

I hope this helps get you started.

jamiefico

Appreciate your help - the formula is working but not giving me the right result, so allow me to provide the full picture with the reference sheet.

This sheet is where I need the formula under the "Payroll/Benefits Need" column, to find the row that has the same project ID# from the HR PROJECTS reference sheet, and bring over the multi-select dropdown column called HR FUNCTION. IF "Benefits" or "Payroll" is selected, equals 1 or else 2.

Here is the HR PROJECTS reference sheet showing the HR FUNCTION column (5th column from ID#) I need to bring over:

Scott Peters

=IF(OR(HAS("Benefits", INDEX({HR FUNCTION}, MATCH([Project ID#]@row, {PROJECT IDS}, 0))), (HAS("Payroll", INDEX({HR FUNCTION}, MATCH([Project ID#]@row, {PROJECT IDS}, 0))))), 1, 2)

jamiefico

Disregard Scott, user error... the formula with HAS is working :) Sorry about that, THANK YOU!

jamiefico

And... good to know about Index/Match over Vlookup, yes I have experienced that pain already so appreciate the insight!

Sign in to join the conversation!

Quick Links

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.