A Smart Vlookup Finction 3

[chiragyagnik]

Hi,

I am completely new to VB and am trying to write a macro in it. I am trying to create a VLOOKUP function that would search more efficiently than the one excel provides.

For example if I had a table below:
Database 1 Database 2
General Motors General Motors Corp
Ford Mot Ford Motors
Microsoft Microsoft

The Vlookup function in excel , when using exact matching criteria, will not match the first two entries in DB1 and DB2 and only match the last one. If i use the Excel's approximate match criteria by using TRUE as my lookup criteria, for large databases it will give me crazy results.

Although there probably is a way to resolve this using a Macro. I tried writing a Macro that will first do the regular Vlookup with exact match criteria and then, for the cells in which there was no exact match found use the search function in excel to search for the First word of my search word and then return and matching results. This should definately improve the existing Vlookup.

I haven't even been able to correctly use the regular Vlookup function in VB properly as when I use my Custom Formula in Excel it gives me crazy results - although some how it works and does not give me any errors.

Here is what I have so far (I know I couldn’t go very far):

Function ivlookup(LookupValue, LookupRange, Column, SearchType)

ivlookup = Application.WorksheetFunction.VLookup(LookupValue, LookupRange, Column, SearchType)

End Function

I tried fixing my vlookup but gave up as I am not even familiar with the most basic syntax and commands in VB. I will sincerely appreciate any Help.

Thanks,
Chirag

 

[johnwm]

Use Help to find the 'LIKE' keyword

Good Luck
johnwm
________________________________________________________
To get the best from these forums read faq731-376 before posting

Steam Engine enthusiasts:

http://www.essexsteam.co.uk

 

[KiwiMace]

An alternative methodology avoids this problem - ensure your data entry is consistent with pull down menus.

If your entries are all truncated like you have demonstrated, you have the option of parsing the most significant part of the word with the Left() function and searching/matching on the left-most part of the text entry.

 

[chiragyagnik]

Thanks Johnwm and CinciMace. I am working on the macro based on your suggestions and will let you know if it works successfully.

I really appereciate your help!

Thanks,

Chirag