+ Reply to Thread
Results 1 to 7 of 7

Lookup and Match UPC for 2 Different Vendors

  1. #1
    Registered User
    Join Date
    10-24-2019
    Location
    Morganfield, Ky
    MS-Off Ver
    Office 2007
    Posts
    3

    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.
    Attached Images Attached Images
    Last edited by kbmhc; 10-24-2019 at 06:22 PM.

  2. #2
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    22,508

    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. #3
    Registered User
    Join Date
    10-24-2019
    Location
    Morganfield, Ky
    MS-Off Ver
    Office 2007
    Posts
    3

    Re: Lookup and Match UPC for 2 Different Vendors

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

  4. #4
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel (Windows 11 64-bit)
    Posts
    58,988

    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.
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!
    Don't forget to say "thank you" to anyone who has offered you help in your thread. You can reward them by clicking on * Add Reputation below theur user name on the left, if you wish.


    Forum Rules (updated September 2018): please read them here.
    How to use the Power Query code you've been given: help here. More about the Power suite here.

  5. #5
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel (Windows 11 64-bit)
    Posts
    58,988

    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. #6
    Registered User
    Join Date
    10-24-2019
    Location
    Morganfield, Ky
    MS-Off Ver
    Office 2007
    Posts
    3

    Re: Lookup and Match UPC for 2 Different Vendors

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

  7. #7
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel (Windows 11 64-bit)
    Posts
    58,988

    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.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Summarizing A List of Vendors
    By saq7792 in forum Excel General
    Replies: 1
    Last Post: 10-30-2017, 05:14 PM
  2. Replies: 1
    Last Post: 04-07-2014, 01:17 AM
  3. Replies: 6
    Last Post: 11-27-2013, 10:28 PM
  4. Analysisi the Top 26 vendors from among all on a new sheet.
    By PRADEEPB270 in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 11-22-2013, 11:21 PM
  5. Automatic emails to vendors with data
    By Manic1525 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 04-15-2013, 03:53 AM
  6. Minimization of numbers of vendors
    By Jowi7991 in forum Excel General
    Replies: 5
    Last Post: 06-18-2012, 09:50 AM
  7. [SOLVED] VBA Code to list vendors
    By Andri in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 02-20-2005, 05:06 PM

Bookmarks

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