+ Reply to Thread
Results 1 to 13 of 13

Match Product Codes with Text

  1. #1
    Registered User
    Join Date
    05-06-2017
    Location
    Perth, Western Australia
    MS-Off Ver
    2010, 2016
    Posts
    5

    Match Product Codes with Text

    Hi Excel Forum Experts,

    Can you please assist me, I'm trying to match the unique product code with the text describing the product.

    A1:C86 is the format of a stock on hand sheet, you can see how the Varietal, vintage and brand of the wine are in different cells.

    G1:G16 are cells that I have copied from another program. The objective is to match the product code in column A with the text in column G and insert the unique product code into say column F.

    I've tried VLOOKUP, Index/Match but they return incorrect values or errors.

    Thanks in advance for any help and this is the first question I've posted on this forum, so apologies for any mistakes.

    Scott
    Attached Files Attached Files
    Last edited by scottiangrieve; 05-15-2017 at 11:19 PM.

  2. #2
    Registered User
    Join Date
    01-19-2017
    Location
    Michigan
    MS-Off Ver
    2013
    Posts
    92

    Re: Match Product Codes with Text

    Attach a sample workbook with desensitized data, as many forum users will NOT go to a file sharing site.

  3. #3
    Registered User
    Join Date
    05-06-2017
    Location
    Perth, Western Australia
    MS-Off Ver
    2010, 2016
    Posts
    5

    Re: Match Product Codes with Text

    Happy to attach a sample workbook, but when writing the thread, the attachment link would not open a dialog box to attach a file. I tested this on Chrome and IE, hence the dropbox link.

  4. #4
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    Office 365 Version 2211, rarely 2007
    Posts
    12,801

    Re: Match Product Codes with Text

    scottiangrieve welcome to the forum.

    To attach a file to your post,
    • be sure to desensitize the data
    • click “Go Advanced” (next to Post Quick Reply – bottom right),
    • scroll down until you see “Manage Attachments”, click that,
    • click “Browse”.
    • select your file(s)
    • click “Upload”
    • click “Close window”
    • click “Submit Reply”

    The file name will appear at the bottom of your reply.
    Dave

  5. #5
    Registered User
    Join Date
    05-06-2017
    Location
    Perth, Western Australia
    MS-Off Ver
    2010, 2016
    Posts
    5

    Re: Match Product Codes with Text

    Hi FlameRetired,

    Thanks for the tips. I've removed the Dropbox link and attached the file.

    I was comfortable with the content of the file, but nevertheless, I've changed the data to adhere to the guidelines more closely.

  6. #6
    Forum Expert leelnich's Avatar
    Join Date
    03-20-2017
    Location
    Delaware, USA
    MS-Off Ver
    '97, 2016
    Posts
    2,523

    Re: Match Product Codes with Text

    Quote Originally Posted by scottiangrieve View Post
    ...match the product code in column A with the text in column G and insert the unique product code into say column F...
    Not sure what you're after, given your data. When you say "match", do you mean FIND or do you mean JOIN TOGETHER? I'm not seeing any "findable" (read "matching") values.

  7. #7
    Registered User
    Join Date
    05-06-2017
    Location
    Perth, Western Australia
    MS-Off Ver
    2010, 2016
    Posts
    5

    Re: Match Product Codes with Text

    Thanks for the reply leelnich,

    I was asked to take out a fair bit of data to desensitize it, but I'll be more specific in my request.

    I've attached the workbook again, why does the code below not work?

    =VLOOKUP(G2,$A$2:$C$7,1,TRUE)

    I want to look up the text "Cabernet Merlot", in the table and return the code which is in column 1 of the table, using an approximate match as the text is different in the table to column G.
    Attached Files Attached Files

  8. #8
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2211
    Posts
    36,749

    Re: Match Product Codes with Text

    Why it doesn't work.

    1. It is looking in column A for the answer.
    2. The phrase "2015 Ferngrove Symbols Cabernet Merlot" does not occur in column A (or anywhere in your data table), so it cannot find a match.

    I suspect you have dumbed-down your sheet too much... Is it really laid out with the brand names that appear (strangely out of place looking) in row 3 (in brackets) are NOT DIRECTLY associated with the actual varietals/vintages???
    Glenn



  9. #9
    Forum Expert leelnich's Avatar
    Join Date
    03-20-2017
    Location
    Delaware, USA
    MS-Off Ver
    '97, 2016
    Posts
    2,523

    Re: Match Product Codes with Text

    Ah! Vlookup works with whole strings. You need to find a string within a string. You're going to need the SEARCH function or FIND function. Give me a few minutes, I'll try to come up with something. Where did you want to put this formula?

  10. #10
    Forum Expert leelnich's Avatar
    Join Date
    03-20-2017
    Location
    Delaware, USA
    MS-Off Ver
    '97, 2016
    Posts
    2,523

    Re: Match Product Codes with Text

    Quote Originally Posted by scottiangrieve View Post
    ...look up the text "Cabernet Merlot", in the table and return the code which is in column 1 of the table, using an approximate match as the text is different in the table to column G.
    It sounds like "the table" refers to range A1:C7. but that's the only place where "Cabernet Merlot" appears as a complete phrase, so you must already know where it is if you're using it in the formula. So why are you searching the table for it?

    Skip this, I figured it out.
    Last edited by leelnich; 05-16-2017 at 04:55 AM.

  11. #11
    Forum Expert leelnich's Avatar
    Join Date
    03-20-2017
    Location
    Delaware, USA
    MS-Off Ver
    '97, 2016
    Posts
    2,523

    Re: Match Product Codes with Text

    Ok scotti, I think I've got it! The following formula looks in G2 for any phrase in column C. If one is found, it returns the corresponding product code from column A. If none is found, returns "" (blank).
    =IFERROR(INDEX($A:$A, MATCH(0,--ISERROR(SEARCH($C:$C,$G2))+($C:$C=""),0),1),"")

    This is an ARRAY FORMULA. Select F2 and paste it in the Formula Bar, then press CTRL+SHIFT+ENTER to confirm entry.
    Now copy and drag down to the bottom of your list.

    Please click the Add Reputation star below any helpful posts, and if you have your answer, mark your thread as SOLVED (Thread Tools up top). Thanks!-Lee
    Attached Files Attached Files
    Last edited by leelnich; 05-16-2017 at 05:10 AM.

  12. #12
    Registered User
    Join Date
    05-06-2017
    Location
    Perth, Western Australia
    MS-Off Ver
    2010, 2016
    Posts
    5

    Smile Re: Match Product Codes with Text

    Hi leelnich,

    Thanks so much for your help, for a large amount of data it is a big help.

    There are some codes it is not picking up which I thought the search would have picked up.

    For example, In column C the text shows "Majestic Cabernet Sauvignon" and in column G the text is "Ferngrove Orchid Majestic Cabernet Sauvignon"

    Is there a way to pick this up or increase the accuracy? Tell me to get lost if I'm pushing my luck

  13. #13
    Forum Expert leelnich's Avatar
    Join Date
    03-20-2017
    Location
    Delaware, USA
    MS-Off Ver
    '97, 2016
    Posts
    2,523

    Re: Match Product Codes with Text

    Strange, it worked fine here, but even one small difference will negate the match. Try this: copy your value in C. Then select the cell in G. In the formula bar, select the characters you expect to match. Press delete. Now press CTRL+V to paste your copied version in the same spot within the larger text and hit ENTER. If you suddenly get a match, there was some difference in the text.

    Also, make SURE the column F cells that aren't returning the right result are ARRAY FORMULAE. Select one, and examine -but do NOT click in- the formula bar. The formula should be surrounded by curly braces {}. This is Excel's way of telling you it's an array form. If you don't see them, click in the formula bar and press CTRL+SHIFT+ENTER. The braces (and the correct result) should appear.

    If this was the problem, select whichever cell you originally entered the formula in, make sure IT'S got the braces {}, then re-copy it down as needed. NOTE: Make sure the copied cell isn't included in the Paste area, otherwise you get an error message "Can't change part of an array". Also, NO you can't just type in the braces{}. CTRL+SHIFT+ENTER is the ONLY way to enter an ARRAY FORMULA.

    Please click the Add Reputation star below any helpful posts, and if you have your answer, mark your thread as SOLVED (Thread Tools up top). Thanks!-Lee
    Attached Files Attached Files
    Last edited by leelnich; 05-17-2017 at 05:42 AM.

+ 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. [SOLVED] (Urgent help) Replacing Product codes with Product name
    By yash.kedia in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 01-21-2016, 06:17 AM
  2. Looking up new bar codes and matching to product.
    By rsuits in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 09-04-2014, 11:48 AM
  3. Data from two sheets, same product codes, match into a new sheet
    By schroeders12 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 08-08-2014, 10:42 AM
  4. Problem with VLookup and Index Match on mixed Text and Numeric codes
    By Vaslo in forum Excel Formulas & Functions
    Replies: 28
    Last Post: 12-17-2013, 12:54 PM
  5. I have two columns (A) and (G) that have like product codes however
    By genesisoxygen@gmail.com in forum Excel Programming / VBA / Macros
    Replies: 12
    Last Post: 08-07-2006, 01:05 PM
  6. I have two columns (A) and (G) that have like product codes however
    By genesisoxygen@gmail.com in forum Excel General
    Replies: 1
    Last Post: 08-04-2006, 03:25 PM
  7. [SOLVED] I have two columns (A) and (G) that have like product codes however
    By genesisoxygen@gmail.com in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 08-04-2006, 03:25 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