| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
vlookup/matching? much help needed!
I have two lists of data that I want to compare to see if there are
any common entries. should I do this using a vlookup or matching function? or other? as below, the data is formatted differently in each list- this first list is a combination of 7 and 10 digit codes, the second has both the 7 and 10 digit code for each entry: List 1 aaaaaaa bbbbbbbbbb cccccccccc ddddddd .. .. .. .. List 2 xxxxxxx xxxxxxxxxx yyyyyyy yyyyyyyyyy zzzzzzz zzzzzzzzzz .. .. .. .. thanks for your help! |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
vlookup/matching? much help needed!
In Sheet1 (assuming list1 is here) in column B and copy down:
=IF(ISNA(MATCH(A2,Sheet2!A:A,0)),IF(ISNA(MATCH(A2, Sheet2!B:B,0)),"","Matched"),"Matched") This will check entries in column A of list1 against columns A &B of list2 (on Sheet2)and put "Matched" in B if a match is found. HTH "jack" wrote: I have two lists of data that I want to compare to see if there are any common entries. should I do this using a vlookup or matching function? or other? as below, the data is formatted differently in each list- this first list is a combination of 7 and 10 digit codes, the second has both the 7 and 10 digit code for each entry: List 1 aaaaaaa bbbbbbbbbb cccccccccc ddddddd .. .. .. .. List 2 xxxxxxx xxxxxxxxxx yyyyyyy yyyyyyyyyy zzzzzzz zzzzzzzzzz .. .. .. .. thanks for your help! |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
vlookup/matching? much help needed!
On Feb 8, 10:55 am, Toppers wrote:
In Sheet1 (assuming list1 is here) in column B and copy down: =IF(ISNA(MATCH(A2,Sheet2!A:A,0)),IF(ISNA(MATCH(A2, Sheet2!B:B,0)),"","Matche*d"),"Matched") This will check entries in column A of list1 against columns A &B of list2 (on Sheet2)and put "Matched" in B if a match is found. HTH "jack" wrote: I have two lists of data that I want to compare to see if there are any common entries. should I do this using a vlookup or matching function? or other? as below, the data is formatted differently in each list- this first list is a combination of 7 and 10 digit codes, the second has both the 7 and 10 digit code for each entry: List 1 aaaaaaa bbbbbbbbbb cccccccccc ddddddd .. .. .. .. List 2 xxxxxxx xxxxxxxxxx yyyyyyy yyyyyyyyyy zzzzzzz zzzzzzzzzz .. .. .. .. thanks for your help!- Hide quoted text - - Show quoted text - cheers, thanks! |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
vlookup/matching? much help needed!
formula does not seem to work correctly. any other suggestions?
=IF(ISNA(MATCH(B3,F:F,0)),IF(ISNA(MATCH(B3,G:G,0)) ,"","matched"),"No") thanks! On Feb 8, 12:46 pm, "jack" wrote: On Feb 8, 10:55 am, Toppers wrote: In Sheet1 (assuming list1 is here) in column B and copy down: =IF(ISNA(MATCH(A2,Sheet2!A:A,0)),IF(ISNA(MATCH(A2, Sheet2!B:B,0)),"","Matche**d"),"Matched") This will check entries in column A of list1 against columns A &B of list2 (on Sheet2)and put "Matched" in B if a match is found. HTH "jack" wrote: I have two lists of data that I want to compare to see if there are any common entries. should I do this using a vlookup or matching function? or other? as below, the data is formatted differently in each list- this first list is a combination of 7 and 10 digit codes, the second has both the 7 and 10 digit code for each entry: List 1 aaaaaaa bbbbbbbbbb cccccccccc ddddddd .. .. .. .. List 2 xxxxxxx xxxxxxxxxx yyyyyyy yyyyyyyyyy zzzzzzz zzzzzzzzzz .. .. .. .. thanks for your help!- Hide quoted text - - Show quoted text - cheers, thanks!- Hide quoted text - - Show quoted text - |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
vlookup/matching? much help needed!
Hi Jack
Try =IF(AND(ISNA(MATCH(B3,F:F,0)),ISNA(MATCH(B3,G:G,0) )),"No","Matched") -- Regards Roger Govier "jack" wrote in message oups.com... formula does not seem to work correctly. any other suggestions? =IF(ISNA(MATCH(B3,F:F,0)),IF(ISNA(MATCH(B3,G:G,0)) ,"","matched"),"No") thanks! On Feb 8, 12:46 pm, "jack" wrote: On Feb 8, 10:55 am, Toppers wrote: In Sheet1 (assuming list1 is here) in column B and copy down: =IF(ISNA(MATCH(A2,Sheet2!A:A,0)),IF(ISNA(MATCH(A2, Sheet2!B:B,0)),"","Matche**d"),"Matched") This will check entries in column A of list1 against columns A &B of list2 (on Sheet2)and put "Matched" in B if a match is found. HTH "jack" wrote: I have two lists of data that I want to compare to see if there are any common entries. should I do this using a vlookup or matching function? or other? as below, the data is formatted differently in each list- this first list is a combination of 7 and 10 digit codes, the second has both the 7 and 10 digit code for each entry: List 1 aaaaaaa bbbbbbbbbb cccccccccc ddddddd .. .. .. .. List 2 xxxxxxx xxxxxxxxxx yyyyyyy yyyyyyyyyy zzzzzzz zzzzzzzzzz .. .. .. .. thanks for your help!- Hide quoted text - - Show quoted text - cheers, thanks!- Hide quoted text - - Show quoted text - |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
vlookup/matching? much help needed!
On Feb 19, 9:47 am, "Roger Govier"
wrote: Hi Jack Try =IF(AND(ISNA(MATCH(B3,F:F,0)),ISNA(MATCH(B3,G:G,0) )),"No","Matched") -- Regards Roger Govier "jack" wrote in message oups.com... formula does not seem to work correctly. any other suggestions? =IF(ISNA(MATCH(B3,F:F,0)),IF(ISNA(MATCH(B3,G:G,0)) ,"","matched"),"No") thanks! On Feb 8, 12:46 pm, "jack" wrote: On Feb 8, 10:55 am, Toppers wrote: In Sheet1 (assuming list1 is here) in column B and copy down: =IF(ISNA(MATCH(A2,Sheet2!A:A,0)),IF(ISNA(MATCH(A2, Sheet2!B:B,0)),"","Matche***d"),"Matched") This will check entries in column A of list1 against columns A &B of list2 (on Sheet2)and put "Matched" in B if a match is found. HTH "jack" wrote: I have two lists of data that I want to compare to see if there are any common entries. should I do this using a vlookup or matching function? or other? as below, the data is formatted differently in each list- this first list is a combination of 7 and 10 digit codes, the second has both the 7 and 10 digit code for each entry: List 1 aaaaaaa bbbbbbbbbb cccccccccc ddddddd .. .. .. .. List 2 xxxxxxx xxxxxxxxxx yyyyyyy yyyyyyyyyy zzzzzzz zzzzzzzzzz .. Still having trouble with this one. I used the formula above (IF(AND(ISNA(MATCH(B3,F:F,0)),ISNA(MATCH(B3,G:G,0) )),"No","Matched") but I get "No" for everything. I know that there should be matches. For example this first list is what is in column B: 9997291093 9997291107 9997291123 9997291131 9997291158 9997291190 9997291204 9997291239 9997291255 9997291263 9997291271 9997291298 9997291301 I know that these items are in columns F or G, but I still get "No" 9997291131 9997291131 999729114X 999729114X 9997291158 9997291158 9997291166 9997291166 9997291174 9997291174 9997291182 9997291182 9997291190 9997291190 9997291204 9997291204 9997291212 9997291212 9997291220 9997291220 9997291239 9997291239 9997291255 9997291255 9997291263 9997291263 9997291271 9997291271 999729128X 999729128X 9997291298 9997291298 9997291301 9997291301 9997291603 9997291603 999729162X 999729162X 9997291638 9997291638 9997291646 9997291646 9997291654 9997291654 9997291662 9997291662 9997291670 9997291670 9997291891 9997291891 9997291913 9997291913 9997291921 9997291921 999729193X 999729193X 9997293975 9997293975 9997293983 9997293983 9997293984 9997293984 9997293991 9997293991 Can you help me think of another way to match these columns? Thanks! .. .. .. thanks for your help!- Hide quoted text - - Show quoted text - cheers, thanks!- Hide quoted text - - Show quoted text -- Hide quoted text - - Show quoted text - |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Sports Comp Ladder tabulating help needed | Excel Worksheet Functions | |||
| A rather difficult & complex statistical search formula needed | Excel Worksheet Functions | |||
| Macro help needed | Excel Discussion (Misc queries) | |||
| sendkey help needed | Excel Discussion (Misc queries) | |||
| formula results take up to 2 lines if needed, but keep border | Excel Worksheet Functions | |||
Hybrid Mode
