Using the VlookUp - Range_Lookup field

[rommelq]

Hi,

In worksheet VAT, i have a list of suppliers in column A but the names are not complete. For example MARK & SP.

In worksheet SUPPLIERS, i have the complete name of the suppliers in column A. For example MARK & MARK, MARK & SPENCER,

I named the array of suppliers as SUPPLIERS.

In worksheet VAT column B , i typed, =vlookup(A1,SUPPLIERS,1,TRUE).

The result was MARK & MARK. How can i refined the vlookup function so that the result would be MARK & SPENCER which is the supplier i am referring to? My list in column A is quite long and i don't want to edit the list. I cannot changed the rang_lookup to FALSE since it would give me an error.

Please help. Thanks.

 

[Pericic]

why don't you use the edit--find----replace command to quickly edit the suppliers list in column A ???? it takes 2 seconds....

for ex: edit---find----"MARK & SP" ---replace -- "MARK & SPENCER"

this seems to be the quickest fix to me....
gl

 

[rommelq]

Thanks for the reply. But i have more than a hundred names in my VAT list. Doing the find and replace procedure would take so much time.

 

[Pericic]

perhaps you can then use an advanced filter on your SUPPLIERS and copy "unique records only" to your column A in worksheet VAT...this way the names you are using in vlookup would match..

 

[rommelq]

Thanks for the advice, but i think it's a little more complicated than that. Anyway, i'll try a work around first to solve my problem. You've been kind to extend your help. I appreciate it.

 

[Tyarra]

Understanding the VLOOKUP is probably key here.

Specifying TRUE for range_lookup will net you something, if all values aren't higher than what you're feeding it. In your case, "MARK & SP" is greater than "MARK & MARK", but less than "MARK & SPENCER" - so, you'll always get whatever value is closest in value, while still be lesser, if there is not an exact match.

Perhaps something like ...

=VLOOKUP(REPLACE(A1,LEN(A1),1,"z"),Suppliers,1,TRUE)

This turns the last character of your incomplete supplier name into "z", which will let you find MARK & SPENCER over MARK & MARK.

Of course, you're looking for MARK & SAUNDERS, you'll miss it. But, this might work for most cases.