Can I use INDEX/MATCH to reference info on the same sheet?

Hello!

I am having trouble finding the right formula to populate my Site Manager column with the name of the right site manager based on criteria in two other columns on the same sheet.

I want the Site Manager column to be populated with the Contact name of the Site Manager for that particular campus.

In the example below where the Campus is Michigan I want it to return the value of Julie, where the campus is Ohio I want it to return a value of Ethan, where the campus is Indiana I want it to return a value of Jasmine. So I need it to return the name in the Contact column when Expertise/Role="Site Manager" for the same Campus of the row that the Site Manger column is on.

I feel like this should be easy but I cannot figure it out!

Find more posts tagged with

Accepted answers

All comments

KPH

Almost!

INDEX is a great start, but as you have two criteria (the Campus must be the same as the Campus on the current row and the Role must be "Site Manager"), you are going to need to use COLLECT instead of MATCH.

Try this:

=INDEX(COLLECT(Contact:Contact, [Expertise/Role]:[Expertise/Role], "Site Manager", Campus:Campus, Campus@row), 1)

It will return the value from the Contact column if the value in the Expertise/Role column is "Site Manager" and the value in the Campus column is the same as the value in the Campus column on the current row.

Courtney S.

Is there a reason why you want the Site manager to be both a Column and also have a Row? It seems like it would be easiest to stop making a row for site managers, and put all the site manager names in the Column alone. I can't think of an obvious formula in Smartsheet to fill in the Site Manager column using the answers you currently have filled in. It might be easiest to make a separate sheet that is a simple table of Campus + Site manager only, that you can use as a lookup table to fill in the Site Manager column.

Courtney S.

alternate idea: look into making Parent / Child rows, one Parent row per campus. You could have the Parent row of each campus list the Site Manager, and for the Site Manager column in the children rows, the children can simply have the =PARENT() function to fill in the Site Manager answer from the parent row.

Devin Lee

INDEX/MATCH is good for a single criteria but when you have multiple like you do use INDEX/COLLECT


=INDEX(COLLECT(Contact:Contact, Campus:Campus, Campus@row, [Expertise/Role]:[Expertise/Role], ="Site Manager"), 1)

Courtney S.

@Devin Lee Nice! JaymeR, the function page for Collect: https://help.smartsheet.com/function/collect

JaymeR

@KPH Thank you! That worked perfect!!

@Courtney S. The actual sheet is a bit different. And what I want to be able to do it set up a Dynamic View for the Site Manager at a given site so that they see all of their site's info. Or I might try an automation. I will hide the Site Manager column. Either way I needed their contact info to be associated with all of the site's data. And if that Site Manager changes I want them to be able to update with their replacement and then the dynamic view or automation will automatically update. If you know of a different or better way to accomplish that I would love to hear as I love hearing all of the different ways to work within Smartsheet!

KPH

Excellent news @JaymeR . I'm glad the formula did what you wanted. Your process sounds good to me. Just make that INDEX COLLECT a column formula, hide the column, and let the formula do all the work!

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.