Searching formula for index/match, searching for matching value on multiple sheets

Hello all,

I'm a newbie and am getting stuck trying to find the correct formula to return an ID associated with a person when searching for this person across multiple sheets. I'm familiar with INDEX/MATCH, but am having trouble figuring out what function to combine with it. Example:

Homer has a record in Sheet A (Activities). We need to add his ID to this record (row).

Homer's ID and related personal information could be found in Sheet B, C, or D.

Would I use an IF/OR statement with INDEX/MATCH? And how would they be combined?

Thank you for any help you can give.

Tags:

Best Answer

  • Mark Cronk
    Mark Cronk ✭✭✭✭✭✭
    Answer ✓

    Hi Kathryn,

    I'd use IFERROR. If the value isn't found in table B default to C and then D. Your formula would be along the lines of:

    =iFERROR( Index/match b), IFERROR( Index /match c), IFERROR(Index/match d), "")))

    Work?

    Mark


    I'm grateful for your "Vote Up" or "Insightful". Thank you for contributing to the Community.

Answers

  • Mark Cronk
    Mark Cronk ✭✭✭✭✭✭
    Answer ✓

    Hi Kathryn,

    I'd use IFERROR. If the value isn't found in table B default to C and then D. Your formula would be along the lines of:

    =iFERROR( Index/match b), IFERROR( Index /match c), IFERROR(Index/match d), "")))

    Work?

    Mark


    I'm grateful for your "Vote Up" or "Insightful". Thank you for contributing to the Community.

  • Kathryn Wells
    Kathryn Wells ✭✭✭
    edited 06/19/21

    Thanks, Mark. I ended up exporting the sheets into Excel because of cell reference limits and got this to work:

    =IFERROR(IFERROR(INDEX('SheetB'!D:D,MATCH('SheetA'!G2,'SheetB'!I:I,0)),INDEX('SheetC'!A:A,MATCH('SheetA'!G2,'SheetB'!F:F,0))),"none")

    Much appreciated.

    Kathryn

  • Mark Cronk
    Mark Cronk ✭✭✭✭✭✭

    Glad you found a solution. Well done. Thank you for contributing to the Community.

    Mark


    I'm grateful for your "Vote Up" or "Insightful". Thank you for contributing to the Community.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!