VLOOKUP correct or incorrect use

[jready2395]

Hello, I am not sure if VLOOK up is the correct function to use but this is my problem.

I have a sheet with serial numbers of products solid of a given time span. The sheet is literally just 1100 rows of unique serial numbers. I have about 10 additional sheets of serial numbers from various product batches of production. I need a way to compare the list of products sold to each sheet of products created to see which ones have actually been sold based on the serial numbers. I have been able to use VLOOKUP by copy and pasting a single row at a time to filtering by data. I am looking to see if there is a way to compare an entire sheet at time to the serial numbers of products sold. All the serial number represent the same type of data, the data just fills multiple columns.

[BadlySpelledBuoy]

Hi and welcome

I think it would be easier to understand the requirement if you could post a sample workbook that demonstrates your data layout and your desired result.

BSB

[jready2395]

I believe i have attached a sample of the work i am trying to accomplish.

[MrShorty]

I don't understand exactly what you are trying to do. Your OP also suggested that the list of serial numbers (represented in your sample in C1:I10?) were spread out over several sheets rather than being in one sheet.

As a guess, I put =COUNTIFS($C$1:$I$10,A1) into K1 and copied into K1:K10. This COUNTIFS() function will count how many times the values in A1 appear in C1:I10. 0 for no matches, 1 if the value appears once, 2 if the serial number appears twice, and so on. Is that what you are looking to do -- simply identify which serial numbers in column A also exist in C1:I10?

[jready2395]

I apologize for the change in data. I had to create a test sheet quickly as to not reveal any actual numbers. Yes, I am simply trying to identify which serial numbers in column A also exist in C1:I10. The COUNTIFS does work, one issue that I have discovered is that if a larger serial number has some digits of a smaller number that match it will produce a "hit." An example of this is if 123456 is a larger serial number and 456 is a shorter one, the function will show 2 matches when really it should just be one. With VLOOKUP i know that you can get an exact match and it can actually show you the cell address of the match. Is there a way to accomplish this feature, were it will show the cell address of the match?

[romperstomper]

COUNTIF will not treat 123456 and 456 as the same number. It will, however, ignore any numbers after the 15th significant digit.

[jready2395]

Thank you all for your help, this will move my work a lot faster!