I have two columns of data, A and B. I would like to merge these columns such that only the unique values are included in the final list. At the moment, I'm using an array formula from https://www.get-digital-help.com/2009/06/16/extract-an-unique-distinct-list-from-two-columns-using-excel-2007-array-formula/:

{=IFERROR(IFERROR(INDEX($A$2:$A$20, MATCH(0, COUNTIF($C$1:C1, $A$2:$A$20), 0)), INDEX($B$2:$B$7, MATCH(0, COUNTIF($C$1:C1, $B$2:$B$7), 0))), "")} 

While this works wonderfully, I'd like to avoid array formulas if at all possible, as they significantly slow our already crappy PCs. We're also not able to use VBA for reasons, so that's not an option either.

Is there any way to do this?

  • 1
    Presumably you don't mean "manually"
    – Yorik
    Aug 31, 2017 at 16:42

2 Answers 2


Well, I found a way to do it that seems to be working for now, but it sure as hell ain't pretty.

Column A has a set of data, say 200 total values.

Column B has a second set of data, say 175 values.

Column C checks if the value in column B is present in column A

 =IF(COUNTIF($A$2:$A$750,B2)>0,"Match","No Match")

Column E has a count that just keeps track of where we are relative to the end of the set of data in column A


Column F has a count that increments whenever Column C indicates a unique value

=IF(C2="No Match",MAX($F$1:F1)+1,"")

Column G is where the two sets of data are combined. First, it lists the values in A. Once it reaches the end of the list and starts finding 0s, it uses the index created in column E to find the first unique value in B and increments up until it reaches the final value.


There's a good discussion of how to extract unique and distinct values at this Excel tutorials website.

And the answer to your question is yes, it just involves using an extra INDEX() for each part. Here is the modification:


I left off the outermost IFERROR(). Hope this helps.

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .