How to use multiple Matches in an Index + Match formula.
Hi community,
Trying to use multiple conditions (matches) to pull data from a table (range).
Currently, the Index + Match formula combination is only able to use ONE match condition.
Can you use 2 or more matches with Index formula?
Best Answers
-
-
You can use an INDEX/COLLECT combo.
=INDEX(COLLECT({Range To Pull}, {1st Criteria Range}, 1st criteria, {2nd Criteria Range}, 2nd criteria), 1)
Answers
-
-
You can use an INDEX/COLLECT combo.
=INDEX(COLLECT({Range To Pull}, {1st Criteria Range}, 1st criteria, {2nd Criteria Range}, 2nd criteria), 1)
-
I thank @Leibel S & @Paul Newcome
The solution almost work, but unable to pull data from cross reference sheets. See fields between {}
All fields are Contact list type with true data.
=IFERROR(INDEX(COLLECT({Person 3}, {CAUSER}, [Department Defect Occurred]@row, {WC}, [Work Center Occurred]@row), 1)
Result: #INVALID VALUE
-
That is basically saying there are no rows that meet your range/criteria sets.
What do you get when you use
=COUNTIFS({CAUSER}, [Department Defect Occurred]@row, {WC}, [Work Center Occurred]@row)
-
Finally it worked. Page needed some time to refresh 100's of cross referenced links and it showed that error.
Once I ran the COINFIFS too, result was = 1 which is exactly the correct result.
Your support is very much appreciated.
Thanks
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.6K Get Help
- 433 Global Discussions
- 136 Industry Talk
- 465 Announcements
- 4.9K Ideas & Feature Requests
- 143 Brandfolder
- 147 Just for fun
- 63 Community Job Board
- 466 Show & Tell
- 32 Member Spotlight
- 2 SmartStories
- 298 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!