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

Ashlie S

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

Matt Lynn-PCG

@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-PCG

@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-PCG

@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.