Using the VlookUp - Range_Lookup field

rommelq

New Member
Joined
Aug 26, 2002
Messages
47
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.
 

Excel Facts

Shade all formula cells
To shade all formula cells: Home, Find & Select, Formulas to select all formulas. Then apply a light fill color.

Pericic

Board Regular
Joined
Nov 11, 2005
Messages
204
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

New Member
Joined
Aug 26, 2002
Messages
47
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

Board Regular
Joined
Nov 11, 2005
Messages
204
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

New Member
Joined
Aug 26, 2002
Messages
47
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

New Member
Joined
Aug 23, 2006
Messages
23
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.
 

Forum statistics

Threads
1,136,993
Messages
5,679,023
Members
419,800
Latest member
spvsr999

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top