Modifying macro to search for string instead of matching value.

Hi everyone,

I have a spreadsheet with 2 worksheets:
- Worksheet 1 is named Inventory
- Worksheet 2 is named Addresses

The Inventory worksheet contains 2 columns, ie:
- column 1 (named Hostname) is a list of names
- column 2 (named SN) is a list of serial number

The Addresses also contains 2 worksheet, ie:
- column 1 (named SN) is a list of serial number
- column 2 (named Address) is a list of addresses

I am using the macro below (which I kindly got from stanleydgromjr) to match the Serial Number from column 2 of the Inventory worksheet to column 1 of the Addresses worksheet, and then copy the associated address (from column 2 of the Addresses worksheet) to column 3 of the Inventory worksheet.

File Inventory - Addresses.xlm is an example.

Please Login or Register  to view this content.

The macro works fine as long as the Serial Number being matched between both worksheets is exactly the same. However sometimes the Serial Number can slightly differ in the second worksheet, for example the Serial Number could be FOC09353DKS in the first worksheet while it could be listed as FOC09353DKS (123423) in the second worksheet. Hence I cannot retrieve the address for Serial Number FOC09353DKS

How could I modify the above macro to search for a string (i.e. FOC09353DKS in this case) instead of matching a value?

Note that actually it does not have to be done through a macro, I attempted to use something like =IF(ISERROR(VLOOKUP...) function without success.

Thanks,
Ant

Hi Ant
You can try this...it appears to do as you require

Please Login or Register  to view this content.