VBA to find instead of Vlookup

[wellous]

Hello Experts,

I have two sheets with two columns in each sheet,
column (A) contain customer name
Column (B) contain the region name



Now, i would like to create a macro to copy the values from sheet1 column (A) and look after it in sheet2 column (A) if found in a string to copy the next value from Sheet2 column (B) which is region name to sheet 1 column (B),
by the way i tried Vlookup but when the customer name is not exactly the same in sheets 1&2 it doesn't work...

example :
customer name in Sheet(1) cell A7 = [ADB] .... i want to get the region name in column B...
while in Sheet(2) the same customer name is = [ADB - African Development Bank]
when i put a standard Vlookup it will not find it - so i need to do FIND instead of Vlookup ...
is it possible

here is the code of the recorded macro :

Please Login or Register  to view this content.

Many thanks for your support,
Wellous,

[romperstomper]

You can use a * as a wildcard with VLOOKUP so it will perform a partial match.

[wellous]

Thanks, i used =VLOOKUP(LEFT(A7,3)&"*",Sheet2!$B$2:$C$1953,2,FALSE) but it's not working all the time,
i also don't know how it use * before the string?

thanks again

[romperstomper]

If you want to match data containing say "ABC" anywhere in it, you would use:
=VLOOKUP("*ABC*",Sheet2!$B$2:$C$1953,2,FALSE)

Without data I can't tell you why it only works some of the time other than to say that your lookup table doesn't contain a cell in the first column that begins with the first three characters of your lookup cell.

[wellous]

Thanks, it's sloving the problem