find number in a data base

[Montu]

I have 12000 data entry in sheet 1 like -
A B C D
Sl No. Application N0. Name Date of Birth
1 012510100052658 A.Mahato 18/10/1952
2 012510100052669 N.Nandi 28/09/1970
Continued to ..... Sl no. 12000.
I have another data whose application has been accepted in sheet 2 like -
Sl No. Application N0. Name Date of Birth
1 012510100012788 B.Biswas 01/07/1982
2 012510100043247 P.Paul 18/02/1962
Now I have to find out Rejected applicant from sheet 1, so I have use a
formula like
=if(index(Appl_No,match(b2,Accept_No,0),match($b$2,$2:$2,0)),"Accepted","Rejected")
Appl_No. = it's a reference of Sheet1 B2:B12001
Accept_No. = it's a reference of Sheet2 B2:end of data of the column.
the result shown as "#N/A", suggest me where is the wrong in my formula &
what is correct. Thanks in advance.

 

[Carim]

Hi,

What about :
=IF(ISNA(MATCH(B2,Sheet2!B:B,0)),"Rejected","Accepted")

HTH

 

[Don Guillett]

Are these LONG numbers formatted as text?
The n/a suggests that it is NOT being found.
Also, can't you just look for a match of b2 in column b
=IF(ISNA(MATCH(b2,sheet2!b:b)),"r","a")

 

[Montu]

Thanks for your suggession, but the following formula is for matching between
two number or data. I need find or search a data into another whole database.
That means I want to search number or text of Sheet2 (Sheet2!b2) into sheet1
(Sheet!1b2:end of data within the column). If get the search or find number
from database (Sheet1) then result show "Accepted" ortherwise "Rejected". Is
there any formula for above problem. Suggest me.

 

[Carim]

Hi,

Following formula does work for both Texts and Numbers :

=IF(ISNA(MATCH(Sheet2!B2,Sheet1!B:B,0)),"Rejected","Accepted")

HTH

 

[Montu]

Hi Carim,
Since I got full support from you for "find number in a data base" on
previous day, therefore I need your help again for the same problem. Last
time I was finding solultion for match 1 data with another data, but this
time I have to match 1 data with two another data. Suppose Sheet 1 is
Application Data, Sheet 2 is Accepted Data & sheet 3 is Rejected Data. Now I
want to match Application Data with Accepted & Rejected data. So I have used
a formula in b2 of Application data sheet as below
=if(isna(match(a1,Accepted,0)),"Rejected",if(isna(match(a1,Rejected,0)),"Accepted",""))
but result shows "Rejected", though a1 number is not find by physically
checked. As per formula it should be show as blank, I don't know where is
wrong in my formula. Please help me.
Note - Accept = reference name of Accepted Data (A1:A200)
Reject = reference name of Rejected Data (A1:A32)