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.

Find more posts tagged with

Formulas

Accepted answers

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

All comments

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

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.