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

Kathryn Wells

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.

Mark Cronk

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

Mark Cronk

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

Kathryn Wells

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

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

Mark