have a sheet w/ list of 80 different 6 digit codes. have a second sheet w/
over 13,000 8 digit codes. Trying to figure out how to lookup in the second
list to see if any of the 13,000 first 6 digits of the 8 digit code match any
of the list of 80. Any ideas?

Do something like this in the 13,000 code sheet::
=vlookup(left(a1,6),sheet1!A:A,1,false)
and copy down.

Adjust your ranges to suit.

Regards,
Fred.

"CapFSU" wrote in message
...
have a sheet w/ list of 80 different 6 digit codes. have a second sheet
w/
over 13,000 8 digit codes. Trying to figure out how to lookup in the
second
list to see if any of the 13,000 first 6 digits of the 8 digit code match
any
of the list of 80. Any ideas?

Assuming List 1 of 80 in Col A of Sheet1 and List 2 in Col A of Sheet2...
Enter this in B1 of Sheet2
=IF(ISNA(VLOOKUP(A1,Sheet1!A:A,1,FALSE)),"Not in List 1","Present in List 1")
and copy down till end of your data

In B1 of Sheet1 you can have
=IF(ISNA(VLOOKUP(A1,Sheet2!A:A,1,FALSE)),"Not in List 2","Present in List 2")

for the reverse

"CapFSU" wrote:

have a sheet w/ list of 80 different 6 digit codes. have a second sheet w/
over 13,000 8 digit codes. Trying to figure out how to lookup in the second
list to see if any of the 13,000 first 6 digits of the 8 digit code match any
of the list of 80. Any ideas?

Try this:

=COUNTIF(Sheet1!A:A,LEFT(A1,6))


"CapFSU" wrote:

have a sheet w/ list of 80 different 6 digit codes. have a second sheet w/
over 13,000 8 digit codes. Trying to figure out how to lookup in the second
list to see if any of the 13,000 first 6 digits of the 8 digit code match any
of the list of 80. Any ideas?

This worked well and as a bonus, let me know that I had some duplicates on
the first sheet. This turned out to be something important to know. Thanks
much teethless.

"Teethless mama" wrote:

Try this:

=COUNTIF(Sheet1!A:A,LEFT(A1,6))


"CapFSU" wrote:

have a sheet w/ list of 80 different 6 digit codes. have a second sheet w/
over 13,000 8 digit codes. Trying to figure out how to lookup in the second
list to see if any of the 13,000 first 6 digits of the 8 digit code match any
of the list of 80. Any ideas?

You're Welcome!


"CapFSU" wrote:

This worked well and as a bonus, let me know that I had some duplicates on
the first sheet. This turned out to be something important to know. Thanks
much teethless.

"Teethless mama" wrote:

Try this:

=COUNTIF(Sheet1!A:A,LEFT(A1,6))


"CapFSU" wrote:

have a sheet w/ list of 80 different 6 digit codes. have a second sheet w/
over 13,000 8 digit codes. Trying to figure out how to lookup in the second
list to see if any of the 13,000 first 6 digits of the 8 digit code match any
of the list of 80. Any ideas?