WorksheetFunction.Match or .Lookup Method or other in Excel VBA

[mongoosse]

I have been looking for a way to lookup a value in column A and find the result in column B. Simple? I thought so, however the problem I am having is that the column I am looking up is not a simple match.

I input a phone number in a textbox, that number is stored in varTelNum. In Column A I have a list of combined country codes and, country codes and state codes that may be unique or overlap with the country codes. In Column B I have a an associated control number. I am trying to find a way to look in Column A for a match in pull up the result from Column B of the same row. The logic is below.

Example 1: varTelNum = 432213422345
Example 2: varTelNum = 437333429539

Column A Column B
43 109
4321 120
43221 121
43223 414
436 415
43661 427

I need to match the maximum number of digits from the variable (left to right) to the longest string (also left to right) and if no match is found go to the next shortest entry. In this Example 1, varTelNum should return the value 121 from Column B.
In this Example 2, varTelNum should return the value 109 from Column B as no other left to right strings match. I keep running into the issue that the routine needs to match left to right and not treat the numbers as integers in the excel columns or csv file. If anyone knows how do this as an excel formula instead, that would also work for the moment.

Thank you in advance.

 

[SkipVought]

Hi,

Seems you would do a VBA FIND, Not Spreadsheet MATCH, inside a For...Next loop...

dim r as range, bFound as boolean
bfound=false
for i = len(YourSearchString) to 1 step -1
  set r = activesheet.columns(1),find(mid(YourSearchString,1,i))
  if not r is nothing then
     bfound=true
     exit for
  end if
next
if bfound then
  'have a match on r

else
  'have no match

end if

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[mongoosse]

Thank you very much for the quick response, this is very lean code. I was expecting a suggestion but the code is appreciated even more.

I was just playing with a strComp funtion I found in Visual Basic to try to match the values. But I will try your method.

Thank you again

 

[SkipVought]

I am inclined to lean toward lean.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]