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!

Find more posts tagged with

Accepted answers

Smartsheet User 99

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.

All comments

Smartsheet User 99

=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.

Kavs

@Smartsheet User 99 Nope it didn't work.

Smartsheet User 99

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.

Kavs

@Smartsheet User 99 :

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.

Smartsheet User 99

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.

Kavs

@Smartsheet User 99 This worked! Thank you so much!!!!!

Smartsheet User 99

Glad it all worked out

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, latest, sort, sortType, lookupSort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.