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
- Customer Resources
- 66.2K Get Help
- 431 Global Discussions
- 152 Industry Talk
- 490 Announcements
- 5.2K Ideas & Feature Requests
- 85 Brandfolder
- 155 Just for fun
- 74 Community Job Board
- 501 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 306 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!