+ Reply to Thread
Results 1 to 6 of 6

Look up a series of values based upon UPC entered.

  1. #1
    Registered User
    Join Date
    12-30-2012
    Location
    Ontario, Canada
    MS-Off Ver
    Google Sheets
    Posts
    43

    Look up a series of values based upon UPC entered.

    Try to explain the best I can.

    First sheet is called 'Products', and the second sheet is called 'Products List'.

    In Sheet: Products
    Column A is Products and Column B is the UPC of that product, and a 3rd column let's say it will be unique each time.

    In Sheet: Products List
    Column A is the description of the product, and column B will be the product UPC.

    So basically in sheet 'Products List' they will enter a UPC of a product in column b, and in column a give it a unique name.

    Now what I would like sheet 'Products' to do is when they enter a UPC in column B, it would look at the column B in the Products List sheet and if that matches, grab the name of whatever is in column A.
    The Products sheet might have like 20 of the same UPC's where in Column C they will enter unique ifo for that particular product.

    I tried vlookup, but I don't think that worked that well. Hopefully someone can help.

    Here's a sample: https://docs.google.com/spreadsheets...it?usp=sharing

    Please note the second sheet 'Products List' will only have one UPC per description. And the 'Products' will have a lot of the same UPC's but with a 3rd column.
    Last edited by FragaGeddon; 03-20-2018 at 09:58 PM.

  2. #2
    Forum Expert Logit's Avatar
    Join Date
    12-23-2012
    Location
    North Carolina
    MS-Off Ver
    Excel 2019 Professional Plus - 2007 Enterprise
    Posts
    6,986

    Re: Look up a series of values based upon UPC entered.

    .
    You will need to create a unique term for Col C Products sheet. Something other than just "Y" and "N". There are way too many duplicates
    in Col A & B & C. VLOOKUP won't work as the data is presently. Nor will any other formula/macro unless there is a column with something that
    is truly unique.

    You can choose to have all UPC numbers different from one another and search by UPC. Or, make Col C data truly unique from one another and then do a search on
    that data.

  3. #3
    Registered User
    Join Date
    12-30-2012
    Location
    Ontario, Canada
    MS-Off Ver
    Google Sheets
    Posts
    43

    Re: Look up a series of values based upon UPC entered.

    Sorry, Column C would be very unique for each product. I updated the file to reflect that. Obviously it would have a larger string of numbers, but that gives you an example.
    And column C would never repeat the same value. Even if you had 100 products with the same UPC, column C would be unique for every single one.

    I edited it a bit. I could do what line 15 does, but if you had 100+ products, this could be very long formula.

    Edit: hopefully now you can edit the spread sheet.
    Last edited by FragaGeddon; 03-20-2018 at 11:30 PM.

  4. #4
    Forum Contributor
    Join Date
    07-06-2013
    Location
    Singapore
    MS-Off Ver
    Excel 2016
    Posts
    192

    Re: Look up a series of values based upon UPC entered.

    kinda confusing on your description. Basically you mean just return result of Product Sheet column A(E.g sauce,pasta) from Product List Sheet when UPC entered in the Product Sheet?
    Then maybe this

    =iferror(index('Product List'!A:A,MATCH(B2,'Product List'!B:B,0)),"")

  5. #5
    Registered User
    Join Date
    12-30-2012
    Location
    Ontario, Canada
    MS-Off Ver
    Google Sheets
    Posts
    43

    Re: Look up a series of values based upon UPC entered.

    Yes that worked great. Thanks a million. I just created another sheet that will probably be hidden and it has a pivot table that sorts the list automatically.
    Just have to figure out how to create a button to print that particular sheet.

  6. #6
    Forum Expert Logit's Avatar
    Join Date
    12-23-2012
    Location
    North Carolina
    MS-Off Ver
    Excel 2019 Professional Plus - 2007 Enterprise
    Posts
    6,986

    Re: Look up a series of values based upon UPC entered.


+ 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. Replies: 15
    Last Post: 04-11-2016, 09:08 PM
  2. [SOLVED] VBA code to look up a list of values based on entered values and return all values.
    By dnwadams in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 09-30-2015, 10:14 PM
  3. Replies: 12
    Last Post: 08-05-2015, 05:15 PM
  4. Data Validation based on the values entered in two other columns.
    By staljunk in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 10-31-2014, 07:32 PM
  5. Replies: 14
    Last Post: 03-10-2011, 12:01 AM
  6. Delete Row based on entered values
    By arsenal123gunners in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 06-27-2010, 04:50 PM
  7. Adding values to a cell based on entered data
    By Tin in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 03-02-2006, 08:55 AM

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