Using Index Match to Reference 2 separate sheets

Trying to write a formula that uses one distinct number and references multiple sheets to bring back information

Best Answer

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Answer ✓

    In that case you would use somethign along the lines of...

    =IFERROR(INDEX({Sheet 1 Pull Range}, MATCH([Match Column]@row, {Sheet 1 Match Range}, 0)), INDEX({Sheet 2 Pull Range}, MATCH([Match Column]@row, {Sheet 2 Match Range}, 0)))

    If you already have the INDEX/MATCH portion figured out, then here is the basic idea to expand the syntax to additional sheets.


    IFERROR(above_statement, sheet_2_index)

    IFERROR(above_statement, sheet_3_index)

    IFERROR(above_statement, sheet_4_index)


    IFERROR(sheet_1_index, sheet_2_index)

    IFERROR(IFERROR(sheet_1_index, sheet_2_index), sheet_3_index)

    IFERROR(IFERROR(IFERROR(sheet_1_index, sheet_2_index), sheet_3_index), sheet_4_index)

    Basically you just keep wrapping in IFERROR functions and entering the next sheet's INDEX/MATCH.


Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!