Index Match - Circular reference

msk5400

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!

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.