VBA for Vlookup Function

[MarkCBB]

Hi There,

This questions is just out of curiosity.

I would like to have a look at the VBA behind the Vlookup function, and if possible make the index column of the data range dynamic.

Has any one done this before. like the wat that Index and Match work together.

 

[Richard Schollar]

Hi Mark

I am afraid I for one don't actually understand what you are asking - is this a question aimed at the WorksheetFunction class or he formula driven VLOOKUP? What VBA behind it? VBA functions and the equivalent worksheet functions are compiled C/C++ so AFAIK there's no way to examine the exact coding.

 

[Jon von der Heyden]

You could do a search of Aladins posts here, there are a number of threads that he has contributed to and explained the uses of VLOOKUP and how it works.

As for the code behind the funcion, as Richard has explained already this isn't actually compiled in VBA. But if you want to try and emulate it then look for VBA methods using Linear search and Binary search.

 

[Jon von der Heyden]

Further, I have started a discussion on the use of H/VLOOKUP versus INDEX&MATCH here. This might give you some idea about how these work and about how to use dynamic column index numbers in VLOOKUP.

 

[MarkCBB]

Hi there guys,

Thanks for the responses, the reason why I am asking is in my mind, there should be a standard Excel Formula function that allows for an additional argument, i.e. what column to index. Example =VLOOKUP(value to look for,Database,Index column of database, column value to return,False/True)

The additional argument is the Index column of the database give the user to choose what column to look for the the value in. this way if). again, when talking about the "column value to return, the user should be able to use a negative number as well. in-case the index column is to the right of the lookup column.

I understand that Index and Match functions get the same result, just wondering why this isnt a standard formula function.


Apologies, this isn't really a question, more looking for understanding why, and if it has been created by someone on VBA before *** a custom function.

Will have a look at the links, thank you.

PS: I still think there should be a Paypay link on this site, so people like me can make donations to guys like you two are are willing to take the time to assist other people, even if the money accumulated goes to a charity etc.

Thanks Again.

Kindest Regards,
Mark Blackburn

 

[Jon von der Heyden]

Hi Mark

I agree that a negative column index # would be good. I don't have the answer as to why it cannot support negative index #. However I do know that writing a UDF in VBA to emulate VLOOKUP's linear and binary search, but support both left-to-right and right-to-left lookup, will invariably be less efficient than using INDEX&MATCH.

 

[MarkCBB]

I see your point, and agrre. Thanks Jon.

Hope the weather in CT is Better than the weather in JHB, thanks again. enjoy the weekend.

Regards,
Mark Blackburn

 

[Jon von der Heyden]

The weather isn't too bad, but perhaps that's because I'm in the northern hemisphere at the moment, indulging in fine English Ale and outstaying my welcome.