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

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.

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???

Originally Posted by scottiangrieve

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

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

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