Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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) |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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) |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 --- |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
find number in a data base | Excel Worksheet Functions | |||
How do I automatically add 1 day to a base number every day? | Excel Worksheet Functions | |||
How do I set the number format to Base 12? | Excel Discussion (Misc queries) | |||
Access data base is able to store any number of records | Excel Worksheet Functions | |||
Can you change the base number from 100 to something else with %'s | Excel Worksheet Functions |