Hi All, new to Smartsheet's looking for some assistance on a formula not sure which one to use.

I am currently using the Index Match to pull data from one report to another and the identifier is the email address. The issue I am having is the reference form I am pulling from will have Team Members on the report twice on occasion if they are a rehire. They will show once as active and then on a different line once as terminated. If there are two entries I want it to pull the Active. I can not remove the duplicates from the reference sheet as there are others in the company that use that form. Here is the current formula I am using

=INDEX({Employee Status}, MATCH(Email@row, {Email}, 0))

Here are the options for Employment Status

Active
Terminated
On leave

Here is the header of the current sheet

Find more posts tagged with

Accepted answers

All comments

Matt Lynn ACT

@Ashlie S Pretty sure you need an index(collect()) rather than an index(match()). The match is just for one criteria while the collect is for more than one.

Something similar to:

=INDEX(collect({Employee Status},{Email}, Email@row, {Employment Status},"Active"),1)

Ashlie S

@Matt Lynn-PCG thank you so much for the fast response when I create a formula like that I am constantly getting the message #INCORRECT ARGUMENT SET. All of my references are set according.

INDEX(COLLECT({Employee Status}, {Email}, Email@row, {Employee Status}, "Active"))

Matt Lynn ACT

@Ashlie S , to confirm, your references are cross sheet references right?

Also I think I left off the 1 for row reference. Try this:

=INDEX(collect({Employee Status},{Email}, Email@row, {Employee Status},"Active"),1)

Ashlie S

@Matt Lynn-PCG thanks for the support that just comes back as #INVALID VALUE

=INDEX(COLLECT({Employee Status}, {Email}, Email@row, {Employee Status}, "Active"), 1)

Matt Lynn ACT

@Ashlie S Sorry it's not working yet. I'm sure something else is the issue. Sometimes the references don't save the column and it causes and error. Or you could have a mismatch in the contact (like the name and email in one but just the name in the other) or maybe one is text and one is a contact type etc. At this point I'd really have to just look/see for myself.

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.