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.
Best 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
-
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.
-
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
-
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
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 64.3K Get Help
- 423 Global Discussions
- 221 Industry Talk
- 462 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 144 Just for fun
- 59 Community Job Board
- 463 Show & Tell
- 32 Member Spotlight
- 1 SmartStories
- 300 Events
- 39 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!