I forgot to mention that the formula is an array formula meaning that instead of just pressing Enter when finishing the formula you must hold down Ctrl and Shift before pressing Enter: If you do it correct Excel will put braces {} around the formula. Don't try to enter these {} manually.

Thanks. I knew it must be an array formula as we are comparing whole columns. If you please explain the formula briefly step by step in the worksheet with actual formula and send me attachment, it would be much easier to understand. Thanks again.

A Christmas present.

Attached Files

Query_3.xlsx (13.1 KB, 10 views)

So so much thanks. Thanks alot for your time.

Hope You are fine and healthy...

'=IFERROR(INDEX($A$2:$A$9;SMALL(IF(COUNTIF($B$2:$B$9;$A$2:$A$9)=1;ROW($A$2:$A$9)-CELL("row";$A$2:$A$9)+1);ROWS($1:1)));"")

IN above function, countif function is used. what, if I use COUNTIF FUNCTION AS "COUNTIF($A$2:$A$9;$B$2:$B$9)=1. I checked entering this changed function of countif but results are not correct. why is this, what is the reason. Moreover, you wrote "=1" at the end of countif, does it mean that the values which are common or in other words equal in both columns which is equivalent to "=1", so "=1" means return true if values in two columns are aequal???? please help. Besides, If you guide me to a link or videos or some easy to understand books downloadable online on "Array Formulas" it would be a great help for me. As For now, I am not proficient in array formulas. I've mentioned my e mail address below.Your reply would really be helpful as always.

Stay healthy

Regards

Asad Iqbal
Asadmir27@gmail.com
Asadmir27@yahoo.com