IF Index/ Match Return Yes or No

Hello,

I am using the below to return Yes or No as true or false matches. I am looking to Confirm if the Contact listed in [Requestor Contact] is also listed in range {Covered Persons List Range 3}. However, It is returning Yes across the board. My range covered 2 columns both in text. Do I need the range to be formatted the same as the search value column? Any guidance is appreciated, thanks.

Current Formula:

=IF(ISERROR(INDEX({Covered Persons List Range 3}, MATCH([Requestor Contact]@row, {Covered Persons List Range 3}, 0))), "Yes", "No")

Find more posts tagged with

Formulas

Accepted answers

All comments

Michael Culley

I think you can do this 2 different ways that will work.

=iferror(if(index({Covered Persons List Range 3}, MATCH([Requestor Contact]@row, {Covered Persons List Range 3}, 0))=[Requestor Contact]@row,"Yes","No"),"No")

or you could use a countif formula instead where you do if countif blah blah equals 1, "yes" else "no".

Let me know if that helps you.

BBIrr41

Thank you @Michael Culley , Unfortunately I am getting a No value for all entries.

Michael Culley

What happens when you change the range to just 1 column? Does that at least work?

BBIrr41

@Michael Culley Nope, I also tried changing the column to text like the range but no luck.

Michael Culley

No I meant you said {Covered Persons List Range 3} covers 2 columns? Or did I misunderstand?

If that range is more than 1 column, try only referencing 1 column.

Screenshots would also help if you can provide that.

BBIrr41

Yes, I did change the range to a 1 column range to test if that would fix the issue but no luck.

Example: {Covered Persons List Range 3} was changed to {Covered Persons List Range 1}, which is just the name column in that sheet.

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, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.