Vlookup function to clean up imported data

[kosherboy]

Hello.
Our company recently encountered a huge problem; every week we get an excel sheet from our supplier with their updated stock list, we then need to check and see what is available from what our customers ordered. The list is extremely long so naturally we use a vlookup function, however, we just noticed that a large amount of the products which are definitely available on the stock list do not work with the vlookup function as the item name doesn't match the lookup value due to a "-" in between characters or a "." and sometimes even leading spaces. I'm sure now you are thinking "Why not just "trim" or "substitute" all the spaces and dashes"? Answer is that i have to submit my order the same way it's on the stock list since the supplier imports it into there database, so if i change the way an item appears in the cell then it will never make it to their system.
I had an idea that might resolve the issue but i'm not sure if it's at all possible:
Is there a way to incorporate the trim function and substitute functions into the vlookup function? Meaning the formula will do all the work in cleaning out the stock list without making any changes to it and it will tell me what's available right away.

I tried having the vlookup function lookup approximate values but the result was way off.

I'm looking forward to hearing all responses

[GeneralDisarray]

YEs - there is - just nest the functions.

Can you post an example?



EDIT:

For just a general example, I put the TRIM and SUBSTITUTE type functions around the lookup-range (I'm using INDEX(MATCH()) instead of VLOOKUP). You would put the 'weird name column' reference inside whatever functions you are looking to used to clean up the names.

like: TRIM(SUBSTITUTE(A:A,"-",""))


{=INDEX(FULL_TABLE,MATCH(GOOD_NAME,SUBSTITUTE(TRIM(WEIRD NAME COLUMN WITH BAD TEXT),"-"," "),0),COLUMN YOU WANT)}

This is an array function - you don't type the { or } - type in the rest and press Cntrl+Shift+Enter - that tells excel this is an array function.

[FDibbins]

Is the location of the extra characters predictable?

[kosherboy]

Here is an example workbook Example.xlsx
The dashes and spaces are never predictable.
Also, a side thing, sometimes the trim and clean functions don't work as the spaces in the item name come from a webpage and would therefore be categorized as a non visible character, i therefore use this formula to remove those stubborn spaces:

Please Login or Register  to view this content.

[FDibbins]

OK so which - if either - list can you modify/add to?

[kosherboy]

YEs - there is - just nest the functions.


EDIT:
For just a general example, I put the TRIM and SUBSTITUTE type functions around the lookup-range (I'm using INDEX(MATCH()) instead of VLOOKUP). You would put the 'weird name column' reference inside whatever functions you are looking to used to clean up the names.
like: TRIM(SUBSTITUTE(A:A,"-",""))
{=INDEX(FULL_TABLE,MATCH(GOOD_NAME,SUBSTITUTE(TRIM(WEIRD NAME COLUMN WITH BAD TEXT),"-"," "),0),COLUMN YOU WANT)}

Dear GeneralDisarray,
Thank you for the formula example.
I'm having trouble working your formula with my example workbook, can you post the formula again using the example workbook?

[kosherboy]

OK so which - if either - list can you modify/add to?

Hi FDibbens,
I would like to modify the stock list, not my order list.

[GeneralDisarray]

Sure.

THis is the array formula I used (see attached)>

Please Login or Register  to view this content.

[kosherboy]

Works amazing! Thanks!
All the best!

[kosherboy]

Dear GeneralDisarray,
i have no words, you really helped us out
I appreciate your help very much

[kosherboy]

Sure.

THis is the array formula I used (see attached)>

Please Login or Register  to view this content.

Hi again.
I feel bad making you crazy but it seems the formula will not work if the lookup value is just a plain number?
Is there any way to modify this complex formula to include numbers?