Index Match - Circular reference

I'm getting a circular reference error with the following formula. I understand why I'd get the error, but how would I approach the following?

For the sake of example, I have 2 columns (ColA and ColB). ColA has an ID number, and ColB has a description. ColA may have multiple records with the same ID number. I want ColB to return the description from elsewhere in the sheet if the the ID in ColA matches any other row's ID.

Formula in ColB:

=INDEX(ColB:ColB, MATCH(ColA@row, ColA:ColA, 0))

Any help is greatly appreciated.

Thanks in advance!

Find more posts tagged with

Accepted answers

All comments

James Keuning

What does "Elsewhere in the sheet" mean? Can you screen shot some anonymized data? Maybe mock up what you want the result to be?

msk5400

Hi James - thanks for the response. What I mean is I want ColB to be populated with the ColB value from another row where ColA matches.

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.