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

Options

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?

Tags:

• ✭✭✭✭✭✭
Options

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.

• ✭✭✭✭✭✭
Options

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.

• edited 06/19/21
Options

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

• ✭✭✭✭✭✭
Options

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!