Compare Columns to find Missing Values and Added Values

[inosent]

I have this formula. This will look to see if a value in Col B is found in Col A, and, if so, report the value in Col A:

=INDEX(A:A,MATCH(B2,A:A,0),)

Here is a data sample

1 -- 18

5 -- 9



7 -- 12

18 -- 24

3 -- 2
24 -- 5




12 -- 3

And here is the result:

18


#N/A



12

24

#N/A
5




3

Note, the values are unsorted, and there are, at times, empty rows in between values.

As you can see, the formula finds the values in Col B that are found in Col A, but the values that are not found in Col A return a "#N/A" result.

What I want to do is turn that around, and have a formula that finds the missing values and shows them.

Thus, I want a result that looks like this:

[empty cell]

9



[empty cell]

[empty cell]

2
[empty cell]





[empty cell]



So, the result I am looking for, after comparing the values in the 2 columns, will return only those values in Col B that are not found in Col A, with no "#N/A" thing showing.

 

[BrianJN1]

Is this what you are seeking:
=IFERROR(INDEX(A:A,MATCH(B2,A:A,0),),"")

 

[inosent]

Yes, that helps!

My formula now is:

=IF(IFERROR(INDEX(A:A,MATCH(B1,A:A,0),),"")="",B1,"")

 

[AhoyNC]

That can probably be shortened to:

[TABLE="width: 246"]
<colgroup><col width="246"></colgroup><tbody>[TR]
[TD="width: 246"]=IF(ISNA(MATCH(B1,A:A,0)),B1,"")

[/TD]
[/TR]
</tbody>[/TABLE]