Index-Match + LEFT Function

[jyu65]

Hello all,

I am working on a spreadsheet with two different reports (on different sheets) from different sources. As such, I want to combine all of this data for these two sheets onto one sheet. The data is for products but on one sheet there are 5000 products and on another there are 8703. Thus, it wouldn't work to sort by alphabetical order.

The formula I have been using that has worked for some of the cells is:

=INDEX(Sheet2!D:D,MATCH("*"&LEFT(A2,10)&"*", Sheet2!A:A, 0))

On Sheet 1, I have "Product A" in cell A2 and what I essentially want it to do is to look through the A column of Sheet 2 to find "Product A" and it's corresponding "Number of Units Sold" value (Column D, Sheet 2).

The issue with this formula is the "Number of Characters" value in the LEFT function. When I set it to 10, it pulls up many of the values and omits some (whose names are less than 10 characters).

Is there something I can replace the 10 with that will just automatically select ALL characters? The reason I have to do this is that a lot of products don't exactly match in product name. For example, there could be a hyphen or a capitalization of a letter that would throw off the exact match function.

If this is difficult to understand, please let me know. I really appreciate your help!

[nigelbloomy]

You could try this. It would select all of the characters in A2 and find any matches in sheet2 that had any of those characters in them.

=INDEX(Sheet2!D:D,MATCH("*"&A2&"*", Sheet2!A:A, 0))

[jyu65]

Thanks for your reply nigelbloomy. I tried plugging it in but it only returns #N/A.

[nigelbloomy]

Can you post a small sample of your worksheet with the problem you are having? You can remove any sensitive data.

[jyu65]

I think I figured it out! I ended up using the VLOOKUP function

=VLOOKUP(A1,Sheet2!$A$2:$E$8702,4,TRUE)

[nigelbloomy]

Are you sure that will work? The "TRUE" at the end will find the closest match, not the exact match.

[jyu65]

I think the problem with putting FALSE is that a lot of my cells are formatted differently so it can't pull up the data. When I assign TRUE, it actually pulls up correct data for the lines I have seen so far. Do you know any other way to find an approximate match? Thanks!

[nigelbloomy]

The wildcard characters should have worked as long as the values you are using are text. True in vlookup is one way to find an approximate match. You can also use 1 or -1 at the end of the match formula.

[FDibbins]

if you are using TRUE, vlookup expects a sorted list. It will then look down that list for what you want...if it cannot find an exact match, it will return the next lowest entry below where it *thinks* an exact match would have been. If your vlookup is giving you the answer you want, I suspect it is purely coincidental.

upload a small (clean) sample workbook (not a pic) of what you are working with, and what your expected outcome would look like.