Attached is a worksheet named "Query". In this worksheet there are two columns "A" and "B" containing numbers in both columns. I want to compare values of one column completely with the values of other column and to see whether there are common values in both the columns. If there are any common values I want excel to show those values. For that, I need a formula. Any help would highly be appreciated....


Regards



Asad Iqbal Mir
Asadmir27@yahoo.com

Attached Files

Query.xlsx (8.7 KB, 8 views)

Take a look at the attached.

Attached Files

Query_2.xlsx (10.4 KB, 11 views)

Bundle of thanks. Helped alot

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, 8 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

..... 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 .....

I don't know what you mean.

What the "=1" means I actually tried to explain in the uploaded file, Sheet1!F14:F23.

I wrote "Excel array formulas" in Google and got about 246,000 hits in 0.46 seconds.