Index/Match in different scenarios on same sheet

sehret

So far I've been successfully working with index/match to pull certain names (PM/Supervisor, Additional CC) when a certain requester is used on a sheet. Now I have a requester that is assigned to two (2) different clients, but the PM/Supervisor and Additional CC are different people per client. How can I incorporate that into the index/match formula?

sehret

I did make it work now as

=INDEX(COLLECT({PM/Supervisor}, {Requester}, Requester@row, {Client}, Client@row), 1)

However, if the PM/Supervisor or Additional Contact column is empty, it gives me error code #CONTACT EXPECTED.

Andy_B

Hi Sehret!

Since you're moving from one to multiple criteria, you'll want to switch to an INDEX/COLLECT formula. To find and return a result for the PM/Supervisor, use a formula like this:

=INDEX(COLLECT([PM/Supervisor]:[PM/Supervisor], Requester:Requester, "John Doe", Client:Client, "Client 2"), 1)

The above formula should populate the cell with Simon Hall.

Here is another variation of this formula that would output the name of the Additional CC:

=INDEX(COLLECT([Additional CC]:[Additional CC], Requester:Requester, "John Doe", Client:Client, "Client 1"), 1)

This would populate the cell with the name James Bark.

Hope this helps! 😄

sehret

It doesn't work for me with the formulas indicated. How do you make it pick the correct client and requester line from a reference sheet? The above snippet is the reference sheet, but the info needs to be pulled into the main/separate SmartSheet.

Adam Murphy

I agree with @Andy_B 's formulas above, except that you are doing cross-sheet formulas, a small tweak to first one should help you with the syntax. Index/collect is the correct choice for what you want to do.

=INDEX(COLLECT({PM/Supervisor}, {Requester}, "John Doe", {Client}, "Client 2"), 1)

The above formula should populate the cell with Simon Hall.

Hope this helps!

sehret

Does not work as a column formula. The main/primary SmartSheet has constantly new lines added through the form feature, so the formula needs to be able to work as a column formula. I don't have the time to rewrite the formula every time.

The requester was assigned to client 1 initially, but is now assigned to client 2. Both clients have different PM/Supervisors and (or not) Additional Contacts to CC assigned. However, I cannot remove the data for the timeframe when requester was assigned to client 1, that would remove or falsify the information.

So it needs to collect data from the reference sheet, if "Requester" is a certain name, then second criteria to look for would be the "client" and then pick the respective PM/Supervisor (or "Additional Contact" - depending on column.

sehret

I did make it work now as

=INDEX(COLLECT({PM/Supervisor}, {Requester}, Requester@row, {Client}, Client@row), 1)

However, if the PM/Supervisor or Additional Contact column is empty, it gives me error code #CONTACT EXPECTED.

sehret

Figured it out. Thanks so much for your assistance! It boiled down to a basic column type issue. I had the Client column as the primary in the reference, which needs to be a text format, but it is actually a drop down in the main sheet.