Find number within two data base
 

Since I got full support from this forum 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,Accept,0)),"Rejected",if(isna(ma tch(a1,Reject,0)),"Accepted",""))

but result shows "Rejected", though a1 number is not found in Accepted &
Rejected data sheet by physically checked (use ctrl+f). 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)

Find number within two data base
 

One alternative to get it:
=IF(ISNUMBER(MATCH(A1,Accept,0)),"Accepted",IF(ISN UMBER(MATCH(A1,Reject,0)),"Rejected",""))
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Montu" wrote:
Since I got full support from this forum 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,Accept,0)),"Rejected",if(isna(ma tch(a1,Reject,0)),"Accepted",""))

but result shows "Rejected", though a1 number is not found in Accepted &
Rejected data sheet by physically checked (use ctrl+f). 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)

Find number within two data base
 

Think a better alternative to use is:
=IF(AND(ISNUMBER(MATCH(A1,Accept,0)),ISNUMBER(MATC H(A1,Reject,0))),"Check",IF(ISNUMBER(MATCH(A1,Acce pt,0)),"Accepted",IF(ISNUMBER(MATCH(A1,Reject,0)), "Rejected","")))

If A1 is somehow found in *both* Accept & Reject,
it'll alert you via returning: "Check"
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---