If a number in one column appears in another

Closed
Report
-
Posts
1864
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
August 7, 2021
-
Hello,

I have column B with a series of invoice numbers.

I need to know if that number appears in column A.

In column C I tried =if(B3=A3:A11511,yes,no) but it does not work AND when I copy the formula down through column B I need it to compare the current B cell to the fixed list of A3:A11511.

Thanks

4 replies

Posts
1864
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
August 7, 2021
803
suppose your data is like this in column A and B from row no 1

hdng1 hendg2
4 8
13 7
18 8
8 19
17 19
9 18
18 20
7 14
16 14
in C2 copy paste this formula

="a"&MATCH(B2,$A$2:$A$10,0)


you will get like this (this will get where it is located)


hdng1 hendg2
4 8 a4
13 7 a8
18 8 a4
8 19 #N/A
17 19 #N/A
9 18 a3
18 20 #N/A
7 14 #N/A
16 14 #N/A

N/A means the number in column B of the same row is not available in column A

I have designed for only 10 rows you extend it to as many rows as you need.
dollar signs are important.

if you want only yes or no answers the formula will be

=IF(ISNUMBER(MATCH(B2,$A$2:$A$10,0)),"yes","no")


please confirm whether thsis is ok
Posts
1864
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
August 7, 2021
803
sorry I forgot to add

copy C2 down. easy way of doing this is
take the cursor to the right bottom of cell C2 where the symbol turns to +
then click that +
I have two columns of numbers. I would like to determine the unique numbers in one column. What is the formula please
Posts
1864
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
August 7, 2021
803
this can be done by using advanced filter. but if you need a formla try thi.

suppose data is like this from A1 to A8

1
2
3
2
1
1
2
3


in B1 copy this formula

=IF(COUNTIF(A1:$A$8,A1)=1,A1,"")
note dollar signs.

copy B1 down . you will get in B1 to B8






1
2
3