ExcelBanter

ExcelBanter (https://www.excelbanter.com/)
-   Excel Discussion (Misc queries) (https://www.excelbanter.com/excel-discussion-misc-queries/)
-   -   vlookup/matching? much help needed! (https://www.excelbanter.com/excel-discussion-misc-queries/129801-vlookup-matching-much-help-needed.html)

jack
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!

Toppers
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!

jack
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!

jack
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 -

Roger Govier
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 -

jack
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 -

Roger Govier
vlookup/matching? much help needed!
 
Hi Jack

As far as I can see, the 2 columns F and G are identical.
The formula should be
=(IF(AND(ISNA(MATCH(B3,F:F,0)),ISNA(MATCH(B3,G:G,0 ))),"No","Matched"))

There was a missing closing parenthesis on my previous posting. Excel
may well have inserted it in the wrong place for you when you copied it
onto your workbook.

With your data and the formula as above, I get
9997291093 No
9997291107 No
No
9997291123 No
9997291131 Matched
9997291158 Matched
No
9997291190 Matched
9997291204 Matched
No
9997291239 Matched
9997291255 Matched
9997291263 Matched
9997291271 Matched
9997291298 Matched
9997291301 Matched


--
Regards

Roger Govier


"jack" wrote in message
oups.com...
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 -

jack
vlookup/matching? much help needed!
 
Cheers! Thanks for your assistance!

On Feb 19, 11:47 am, "Roger Govier"
wrote:
Hi Jack

As far as I can see, the 2 columns F and G are identical.
The formula should be
=(IF(AND(ISNA(MATCH(B3,F:F,0)),ISNA(MATCH(B3,G:G,0 ))),"No","Matched"))

There was a missing closing parenthesis on my previous posting. Excel
may well have inserted it in the wrong place for you when you copied it
onto your workbook.

With your data and the formula as above, I get
9997291093 No
9997291107 No
No
9997291123 No
9997291131 Matched
9997291158 Matched
No
9997291190 Matched
9997291204 Matched
No
9997291239 Matched
9997291255 Matched
9997291263 Matched
9997291271 Matched
9997291298 Matched
9997291301 Matched

--
Regards

Roger Govier

"jack" wrote in message

oups.com...
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

roups.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 -- Hide quoted text -

- Show quoted text -- Hide quoted text -

- Show quoted text -


All times are GMT +1. The time now is 10:13 PM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com