INDEX MATCH MATCH with Contacts List
Hello,
I'm trying to use index match match to pull contacts based on 2 different columns. Many of the contacts pull up but there a few that show #INVALID VALUE. I checked to make sure the data was filled properly and everything looks good. Anything I can do to fix this?
My formula is: =INDEX({Program Area Contacts Range 1}, MATCH(Discipline@row, {Program Area Contacts Range 2}, MATCH(Market@row, {Program Area Contacts Range 3})))
Result:
When it should be pulling:
Appreciate the help!
Best Answer
-
First, when it comes to matching multiple criteria, I personally find using MATCH MATCH gets messy, so I usually use INDEX(COLLECT() formula.
=INDEX(COLLECT({Meh Range 1}, {Meh Range 2}, Discipline@row, {Meh Range 3}, Market@row), 1)
Formula Breakdown
Contact Sheet
Master Sheet
I hope this helps out.
Answers
-
=INDEX({Program Area Contacts Range 1}, MATCH(Discipline@row, {Program Area Contacts Range 2}, MATCH(Market@row, {Program Area Contacts Range 3})),0)
See if this works.
-
@Smartsheet User 99 Nope it didn't work.
-
hmmm, can you send a screenshot that includes some that are working and why some aren't? Hiding any sensitive information? I tried drafting a mock-up version of the screenshot, and I didn't get an error.
-
Here is the sheet I want the contacts pulled into using "Discipline" and "Market" to match the contact. I filtered the contact list to show the invalid values:
Here is a screen shot of the contact list sheet...I took off the text showing the email address for privacy. The cells that are empty actually say "none" as there is no current contact for that discipline:
The contact's not showing up is only for the TXWAC market for some reason. Not sure what I'm doing wrong.
-
First, when it comes to matching multiple criteria, I personally find using MATCH MATCH gets messy, so I usually use INDEX(COLLECT() formula.
=INDEX(COLLECT({Meh Range 1}, {Meh Range 2}, Discipline@row, {Meh Range 3}, Market@row), 1)
Formula Breakdown
Contact Sheet
Master Sheet
I hope this helps out.
-
@Smartsheet User 99 This worked! Thank you so much!!!!!
-
Glad it all worked out
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63.9K Get Help
- 410 Global Discussions
- 219 Industry Talk
- 457 Announcements
- 4.7K Ideas & Feature Requests
- 143 Brandfolder
- 136 Just for fun
- 57 Community Job Board
- 459 Show & Tell
- 31 Member Spotlight
- 1 SmartStories
- 298 Events
- 37 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!