INDEX_MATCH equation help

Hi. I have 2 master data sheets (Sheet A and Sheet B). Both sheets have a column called [Company Name]. On Sheet A, i need an equation that looks at [Company Name] in Sheet A, then searches for a match in Sheet B. If there is a company name match in Sheet B, then I would like to return the value of a different column [Company Type] from Sheet B. If there is no match, then I would like to return a blank (or a default text that I specify). The equation below works for returning the right value when there is a match, but when there is no match (e.g. Company name is in Sheet A, but not in Sheet B), then the equation returns "#NO MATCH".

=INDEX({Sheet B Company Type}, MATCH([Company Name]@row, {Sheet B Company Name}, 0))

Can anyone tell me how to modify the equation so that instead of "#NO MATCH" it returns either a blank OR a specific text that I can specify (e.g. "No Company Type Available").

Thanks in advance.

Find more posts tagged with

Accepted answers

All comments

DKazatsky2

Hi @Karenmst,

You should be able to wrap the formula in IFERROR.

=IFERROR(formula, value if formula has error)

=IFERROR(INDEX({Sheet B Company Type}, MATCH([Company Name]@row, {Sheet B Company Name}, 0)), "value to display")

Hope this helps.

Karenmst

Thank you so much. This did the trick! I'll need to remember this one for next time as this will come in handy often.

Thanks again, much appreciated :)

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.