Cross Referencing Data Between Sheets


I need to find a function that I can use to simplify some enrollment data reporting. Rather than entering it in manually...every should auto-update based on what enrollment data is entered in another sheet.

Looking at the pictures the ENRL (Experimental) column, I would like it to return the enrollment number from a different sheet if the CRN in column 1 matches the CRN in the enrollment sheet.

Is this possible? Thanks!

Best Answer

  • Jeff Reisman
    Jeff Reisman ✭✭✭✭✭✭
    Answer ✓

    INDEX/MATCH can handle this for you.

    As your begin your INDEX formula, Smartsheet will pop up a helper screen. Click Reference Another sheet, and select your enrollment sheet, then select the Enrolled column. You can choose to name this range if so desired. Same thing for the CRN column. This process creates the references for your formula.

    =INDEX({Enrollment Sheet Range 1}, MATCH([Course/CRN]@row, {Enrollment Sheet Range 2}, 0))

    In English, Get me the value in the Enrolled column from the Enrollment sheet where the Course/CRN number on this row = the CRN number on the Enrollment sheet.

    Since it appears you don't have a valid entry for Course/CRN on every row, you'll want to wrap the formula in IFERROR to prevent #NO MATCH errors.

    =IFERROR(INDEX({Enrollment Sheet Range 1}, MATCH([Course/CRN]@row, {Enrollment Sheet Range 2}, 0))"")


    Jeff Reisman

    Link: Smartsheet Functions Help Pages Link: Smartsheet Formula Error Messages

    If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!


Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!