vlookup/matching? much help needed!

J

jack

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

Toppers

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
 
J

jack

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









- Show quoted text -
Click to expand...

cheers, thanks!
 
J

jack

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

Roger Govier

Hi Jack

Try
=IF(AND(ISNA(MATCH(B3,F:F,0)),ISNA(MATCH(B3,G:G,0))),"No","Matched")

--
Regards

Roger Govier


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

jack

Hi Jack

Try
=IF(AND(ISNA(MATCH(B3,F:F,0)),ISNA(MATCH(B3,G:G,0))),"No","Matched")

--
Regards

Roger Govier


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!
Click to expand...
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!
 
R

Roger Govier

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


Hi Jack

Try
=IF(AND(ISNA(MATCH(B3,F:F,0)),ISNA(MATCH(B3,G:G,0))),"No","Matched")

--
Regards

Roger Govier


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!
Click to expand...
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!
 
J

jack

Cheers! Thanks for your assistance!

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






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!




- Show quoted text -- Hide quoted text -

- Show quoted text -
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Multiple conditional formulas 0
VLookup Function HELP 7
Vlookup with an Arrayformula.... HELP! 0
Excellent code now a question of Feasibility 7
Vlookup - Returning 2nd match 4
VLOOKUP matches 17
Need a lookup formula that matches 2 values and returns the 3rd va 3
Auto Populating and Formatting Data 7

Top