Skip to content
  • Try Smartsheet

    • Formulas and Functions

    Formulas and Functions

    Ask for help with your formula and find examples of how others use formulas and functions to solve a similar problem.

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

    ✭✭
    in Formulas and Functions

    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!

    image.png
    · Share on FacebookShare on Twitter

    Sign in
    Try Smartsheet

    Answers

    • Community Champion

      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.

      · Share on FacebookShare on Twitter
    • ✭✭✭✭✭

      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.

      · Share on FacebookShare on Twitter
    • ✭✭✭✭✭

      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.

      · Share on FacebookShare on Twitter
    • ✭✭✭✭✭ 
      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)
      · Share on FacebookShare on Twitter
    • ✭✭✭✭✭
      edited 06/07/24

      @Devin Lee Nice! JaymeR, the function page for Collect: Site faviconCOLLECT Function | Smartsheet Learning Center

      · Share on FacebookShare on Twitter
    • ✭✭

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

      · Share on FacebookShare on Twitter
    • Community Champion

      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!

      · Share on FacebookShare on Twitter

    Sign in
    Try Smartsheet

    Help Article Resources

    Create and edit formulas in Smartsheet

    Formula combinations for cross sheet references

    Smartsheet functions list

    Quick Links

    Categories

    Want to practice working with formulas directly in Smartsheet?

    Check out the Formula Handbook template!

    Trending in Formulas and Functions