How to identify values that are found in variable_1 but are not found in variable_2?

[Mirisage]

Hi Forum,

I have two variables, called "Income_1" and "Income_2" (please see the attached).

Question:
I wanted to return what are the values that are found only in Income_2 variable but are not found in Income_1 variable?

Answer:
Answer is provided under "Answer" column.

I have found the answer manually. Would there be any automated way to generate the numbers in the "Answer" column.
As I have several thousand observations in my original dataset, I cannot do this manually.
Thanks
Mirisa

[JeteMc]

Try the following array entered formula*:

Please Login or Register  to view this content.

*Array entered formulas are activated by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer.
Let us know if you have any questions.

[Mirisage]

Hi JeteMc,
Thank you very much for your answer which worked great!
However, I cannot understand how are the results are generated by your equation. I googled to get some understanding. I found some bits and pieces from google but not fully understand how your equation works. I know <> implies not equal to. IFERROR together with last most "" in your equation is to avoid returning N/A. But Could you help me to fully understand how does your equation generate correct results. Thanks

[JeteMc]

One of the best ways that I know to see how a formula works is to select a cell, say D2, of the file attached to post #2, and run Evaluate formula from the Formula tab. You'll notice that the first thing the formula does is produce an array of TRUE/FALSE values based on whether or not the values in column A are also found in column B. Next you'll see that the formula has merged the numbers in column B with the TRUE/FALSE array producing an array that keeps all of the FALSE's but replacing the TRUE's with the corresponding numbers. Next the ROW function returns a number, in this case 6, that the SMALL function uses to pick the number, and in this case the 6th smallest number is 18. I hope that explanation is helpful.
Let us know if you have any questions.

[Mirisage]

Hi JeteMc,
Thank you so much for your time and expertise.

Regards

Mirisa

[JeteMc]

You're Welcome. Thank You for the feedback and for marking the thread as 'Solved'. I hope that you have a blessed day.