# Lookup and Match UPC for 2 Different Vendors

1. ## Lookup and Match UPC for 2 Different Vendors

Hey Everybody,

Long time reader, first time poster.

I am trying to write a formula to lookup multiple vendor SKU numbers after a UPC is given. I want to scan a barcode on a product and have excel look through an ODBC data set for that UPC, and then give me the corresponding vendors SKU. In my system, one vendor is assigned vendor number 66 the other is vendor number 499. Each vendor has their own SKU for the same exact product with the same UPC. Currently I have to scan these barcodes in 2 different spots to get each vendors SKU. I have included screenshots of the built table and my data set. My idea is that I want excel to search for the UPC in column A, then look over in column I to see if the vendor number is 66 or 499, then return the data in column J based on what it finds in column I.

I have tried several formulas to make this work, this one returns the correct SKU for vendor 66 in cell B2

=IF(Data!I:I=66,(INDEX(Data!J:J,MATCH(A2,Data!A:A,0))))

But when I copied the above formula to B3 and changed the vendor number to 499 (exact formula below) it give a reading of FALSE.

=IF(Data!I:I=499,(INDEX(Data!J:J,MATCH(A2,Data!A:A,0))))

I am completely out of ideas, cannot think of anything else to try.

2. ## Re: Lookup and Match UPC for 2 Different Vendors

We can't do anything with a picture - attach a sample Excel workbook instead (see the yellow banner at the top of the screen for details of how to do this).

Hope this helps.

Pete

3. ## Re: Lookup and Match UPC for 2 Different Vendors

Here is a small example of what I am trying to do.

4. ## Re: Lookup and Match UPC for 2 Different Vendors

Just about to look at the workbook, but can already see form the image that you have merged cells - this will most likely be the source of your woes.

5. ## Re: Lookup and Match UPC for 2 Different Vendors

Actually, the merges don't matter here, luckily.

This for the first SKU:

=LOOKUP(2,1/((Data!\$A\$1:\$A\$6=Ordering!A2)*(Data!\$I\$1:\$I\$6=66)),Data!\$J\$1:\$J\$6)

and this for the second:

=LOOKUP(2,1/((Data!\$A\$1:\$A\$6=Ordering!A2)*(Data!\$I\$1:\$I\$6=499)),Data!\$J\$1:\$J\$6)

6. ## Re: Lookup and Match UPC for 2 Different Vendors

Thank you so much!!!! That worked perfectly!

7. ## Re: Lookup and Match UPC for 2 Different Vendors

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.

##### Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1