Vlookup & IF

Hi team,

I am trying to refer to another sheet which has active and inactive employees on it, some are returned employees so their name is twice on the sheet. However I only want the Vlookup to refer to Active employees.

I've tried these options with no success -

=IF({HRMS Current & Ex Employees Range 1} = "Active", VLOOKUP([Full Name]@row, {HRMS Current Employees Range 2}, 19, false), 0)

=IF({HRMS Current & Ex Employees Range 1}@row = "Active", VLOOKUP([Full Name]@row, {HRMS Current Employees Range 2}, 19, false), 0)

=IF({HRMS Current & Ex Employees Range 1} = "Active", VLOOKUP([Full Name]@row, {HRMS Current Employees Range 2}, 19, false))

This formula works fine but doesn't always refer to the active employee -

=VLOOKUP([Full Name]@row, {HRMS Current Employees Range 2}, 19, false)

Any help appreciated!

Find more posts tagged with

Accepted answers

Kelly Moore

Hey @Josephine

An INDEX/COLLECT will accomplish what you need. The range in this function combo will be single columns, not a table as used in VLookUp.

=INDEX(COLLECT({source sheet employee column (what was 19 in table)}, {source sheet employee column to match to (what was 1 in table)},[Full Name]@row,{source sheet status column}, "Active"),1)

Will this work for you?

Kelly

All comments

Kelly Moore

Hey @Josephine

An INDEX/COLLECT will accomplish what you need. The range in this function combo will be single columns, not a table as used in VLookUp.

=INDEX(COLLECT({source sheet employee column (what was 19 in table)}, {source sheet employee column to match to (what was 1 in table)},[Full Name]@row,{source sheet status column}, "Active"),1)

Will this work for you?

Kelly

Josephine

Thank you! worked perfectly and I now know about INDEX/COLLECT!

Kelly Moore

Great! The Index/Collect is a powerful function combo. Another formula to explore is INDEX/MATCH which is used when one has only one criteria to consider. Both Index/Match and Index/Collect may increase sheet performance over the VLookUp.

Kelly

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.