Index Match with multiple ranges?
Hi All,
I am stumped on a formula and hoping for some direction. I am attempting to put together an Index(match) that will bring in the "order number" from sheet two to the "Assigned to Contract" column, if the unique identifier appears in any of the "Unique ID columns" (Columns in between hidden for better viewing).
Is there a way to do an Index Match with multiple ranges?
Thanks in Advance!
Best Answer
-
Hello,
Try out this function:
=IF(ISBLANK([Unique ID]@row),"",INDEX(range of values you want, MATCH([Unique ID]@row,column to evaluate,0)))
INDEX(MATCH()) when you have 1 criteria and dynamic column positions.
Otherwise:
INDEX(COLLECT()) when you have multiple criteria, dynamic column positions, or to return a non-first position"
If you use multiple columns within the range of values you want, you will need to specify which column to pull data from at the end of the INDEX function.
INDEX(COLLECT()) is similar to SUMIFS(). It might look intimidating at first, but it's simple for an advanced function.
Answers
-
Hello,
Try out this function:
=IF(ISBLANK([Unique ID]@row),"",INDEX(range of values you want, MATCH([Unique ID]@row,column to evaluate,0)))
INDEX(MATCH()) when you have 1 criteria and dynamic column positions.
Otherwise:
INDEX(COLLECT()) when you have multiple criteria, dynamic column positions, or to return a non-first position"
If you use multiple columns within the range of values you want, you will need to specify which column to pull data from at the end of the INDEX function.
INDEX(COLLECT()) is similar to SUMIFS(). It might look intimidating at first, but it's simple for an advanced function.
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.2K Get Help
- 360 Global Discussions
- 199 Industry Talk
- 427 Announcements
- 4.4K Ideas & Feature Requests
- 136 Brandfolder
- 127 Just for fun
- 128 Community Job Board
- 444 Show & Tell
- 28 Member Spotlight
- 1 SmartStories
- 283 Events
- 35 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!