Compare 2 lists..

[3G]

Hello-

I've got a list of Vendor IDs (Col A) & Product IDs (Col G). The Vendor ID is 6 digits long, starting with 1 (10000). I have a list of product IDs for these Vendors that I created using the Vendor ID, also 6 digits long, but, the last number increments up if they have multiple products (i.e. 10001, 10002; 20001, 30001...etc). What I'm trying to do is now map the Vendor ID to the product ID, but have been having some problems. Here's what I've tried:


=IF((LEFT(G$2,2))=(LEFT(A2:A5,2)),A$2,"")

-Take the first two digits of each, and, if they're equal, return the vendor ID.


=(MATCH((LEFT(G2,2)),A2:A23,0))-1

-Tell me the row number if they match, then subtract 1 because the data starts in row 2. That'd give me the actual row number.


Both throw errors.


Any help is appreciated!


Thanks

 

[Luke M]

So, the key part of each number is really the 1st two digits? But then, if only the first two digits matter, isn't the vendor ID simply "take first 2 digits of product ID and tack on 4 zeros"? It's confusing as to how the vendor and product ID are both only 6 digits, as it seems information is being lost. For instance, if I was using our names as vendor ID's, the product codes would be:

3G01

3G02

3G03

LUKE01

LUKE02

LUKE03

etc.


Can you clarify what part of the product ID you need to match in the Vendor ID column? I have a feeling the array formula will eventually look something like this:

=INDEX(A:A,MIN(IF(LEFT(A2:A100,2)=LEFT(G2:G100,2),ROW(A2:A100))))

 

[3G]

Luke = the man.


You are right. Ha! You're original suggestion got it to work.


Thanks!