×
INTELLIGENT WORK FORUMS
FOR ENGINEERING PROFESSIONALS

Log In

Come Join Us!

Are you an
Engineering professional?
Join Eng-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Eng-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Jobs

A Smart Vlookup Finction
3

A Smart Vlookup Finction

A Smart Vlookup Finction

(OP)
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

RE: A Smart Vlookup Finction

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: www.essexsteam.co.uk

RE: A Smart Vlookup Finction

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.  


RE: A Smart Vlookup Finction

(OP)
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

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Eng-Tips Forums free from inappropriate posts.
The Eng-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Eng-Tips forums is a member-only feature.

Click Here to join Eng-Tips and talk with other members!


Resources