Vlookup variation

[Yandros]

Hi All,

The Vlookup function normally looks for exact matches.

However I'd like to try and modify the functionality to
look for near matches. For example if I have a two tables
of numbers of where the lengths vary I want to find if
one number appears within another and make a columns
selection based on that.

For example if 447385 exists in array and I have
4473854568 as the lookup up value I only want it to
consider the first few digit (from left to right) which
equal the value in the array.

Is this possible? I have tried :-

Vlookup(right(A2,6),mgt,3,true)

Where A2 is the cell of the value to lookup,
Where MGT is the array (made up of three columns)

Unfortunately it doesn't work even when both sets of
values are sorted numerically on the first column. Beside
which the length of 6 characters can change for each of
the values to be looked up.

I need a query that will examine each value to be looked
up count the number of digits, then look in the array for
the matching digits (ignoring any additional digits after
the intial match in digits is found).

Hope this make sense to someone. Any suggestions would be
gratefully received.

Thanks in advance.

Yandros

 

[Ron Rosenfeld]

Hi All,

The Vlookup function normally looks for exact matches.

However I'd like to try and modify the functionality to
look for near matches. For example if I have a two tables
of numbers of where the lengths vary I want to find if
one number appears within another and make a columns
selection based on that.

For example if 447385 exists in array and I have
4473854568 as the lookup up value I only want it to
consider the first few digit (from left to right) which
equal the value in the array.

Is this possible? I have tried :-

Vlookup(right(A2,6),mgt,3,true)

Where A2 is the cell of the value to lookup,
Where MGT is the array (made up of three columns)

Unfortunately it doesn't work even when both sets of
values are sorted numerically on the first column. Beside
which the length of 6 characters can change for each of
the values to be looked up.

I need a query that will examine each value to be looked
up count the number of digits, then look in the array for
the matching digits (ignoring any additional digits after
the intial match in digits is found).

Hope this make sense to someone. Any suggestions would be
gratefully received.

Thanks in advance.

Yandros

Do all the entries in array have the same number of digits?

If so, then the problem is easy. Your solution does not work because you are
using a text function, which returns a text string. In addition, you are
returning the right 6 characters where you want to return the left six
characters, according to what you describe.

You need to change your lookup_value.

Instead of right(A2,6), you should use either:

--LEFT(A2,6)
VALUE(LEFT(A2,6)
INT(A2/10^6)




--ron

 

[JE McGimpsey]

First, you need to use LEFT() rather than RIGHT().

Second - you don't say how to determine what the length of the lookup
table entries is. If you assume that all entries are the same length,
you can use something like

LEN(INDEX(mgt,1,1))


Third, LEFT() returns a text value. If your lookup table is numeric, you
need to coerce the text to a number. One way:

=VLOOKUP(--LEFT(A2,LEN(INDEX(mgt,1,1))),mgt,3,TRUE)


BTW - The VLOOKUP function normally DOES NOT look for exact matches - by
default it looks for approximate matches. You have to specify FALSE in
the 4th argument to look for an exact match.

 

[CLR]

Hi Yandros.........

You're almost there with your idea and your formula..........only thing
is........if you want the Leftmost 6 characters of the 10 character
sequence, then modify your formula to read

=Vlookup(LEFT(A2,6),mgt,3,true)

Vaya con Dios,
Chuck, CABGx3

 

[Yandros]

Ron,

Thank you for taking the time to respond to my dilemma.

Unfortunately, after trying each of your suggestions,
none returned a usable result.

The values in the array are not of fixed length. They can
be anything from 6 to 16 digits long. This is also true
of the lookup value. These can also vary in length. I was
hoping the there was a way take the length of the lookup
value and use this to determine how many digits should be
considered when looking at the values in the
corresponding column in the array.


Kind regards,

Yandros

 

[Yandros]

JE,

Thank your for your efforts.

Unfortunately, the problem is still not resolved.

The values in the array are not of fixed length. They can
be anything from 6 to 16 digits long. This is also true
of the lookup value. These can also vary in length. I was
hoping the there was a way take the length of the lookup
value and use this to determine how many digits should be
considered when looking at the values in the
corresponding column in the array.

I can not change the values in the lookup table. However,
it may be possible to pad out the values in the Array
with zeros to make them all the same length, but I would
need some guidance on how to do this as it will need to
vary the number of zeros depending on the existing length
of the field. Any ideas on this?

Best regards,

Yandros

 

[Yandros]

Chuck,

Thanks for your suggestion.

Unfortunately, the length is not always 6. This was just
an example I gave. The length can vary between 6 and 16
from cell to cell in the lookup table. This is also true
for the array.

Thanks anyway. I do appreciate all inputs I have received.

Yandros

 

[Ron Rosenfeld]

The values in the array are not of fixed length. They can
be anything from 6 to 16 digits long. This is also true
of the lookup value. These can also vary in length. I was
hoping the there was a way take the length of the lookup
value and use this to determine how many digits should be
considered when looking at the values in the
corresponding column in the array.

There is. And it would have been helpful if you had supplied this information
initially. You would have received a helpful answer more quickly.

1. Given your parameters, the 'numbers' will need to be entered as TEXT.
Excel can only handle numbers up to fifteen digits.

2. If, due to varying lengths of values in the array, there are multiple
matches of lookup_value, how do you want this resolved?

3. Do ALL of the digits in lookup_array need to be considered?

I think if you give a bunch of examples of lookup_values and how you want to
have it matched to array, that would be helpful in devising a solution to your
problem.




--ron

 

[Yandros]

-----Original Message-----


There is. And it would have been helpful if you had supplied this information
initially. You would have received a helpful answer more quickly.

1. Given your parameters, the 'numbers' will need to be entered as TEXT.
Excel can only handle numbers up to fifteen digits.

2. If, due to varying lengths of values in the array, there are multiple
matches of lookup_value, how do you want this resolved?

3. Do ALL of the digits in lookup_array need to be considered?

I think if you give a bunch of examples of lookup_values and how you want to
have it matched to array, that would be helpful in devising a solution to your
problem.




--ron

Ron,

Again thank you for taking the time. I shall consider
myself reprimanded for not providing more details at the
onset. I was conscious that if the message and outlining
details were too long no one would be bothered to read it.

Responding to your points:-

1. Numbers have now be converted to text.

2. If multiple matches - This should not occur as each
value in the array is unique. The value being looked up
is 98% likely to have more digits. If by chance a muliple
match does occur a #NA should be returned, as would be
the case if no match was found.

3. Yes, all values in the lookup array need to be
considered.


Below are the values to be looked up in the array

Column A
2259680
3528900095
3548800090
46548903696
48235489097
666902195699
790295995994
8529898999942
99898994694531
9997191234899352
9998321646413185


Below is a sample of the array (MGT)

Column A Column B Column C
2259 UZBWLS Wesless, Uzbekistan
3528 USALLG Local Line Gate, USA
35488 MRTNLT Network line Trunk, Mauritius
465489 NPLNET Net Pro Ltd, Nepal
482354 STATRL Tralin, Stasbourg
66690 HOLIDY Idye, Holland
7902 BTGPLS Pleasure, British Territory
8529 PRYRIS T-Mobile, Priory
998 BLROIT T-Mobile, Belarus
9997 GRAPTT PTT, Greenland
9998 GRAPTT PTT, Greenland
9999 GRAATC Advanced Transp. Co., Greenland

As mentioned above, in the vast majority of cases, the
number being looked up will have more digits than that
being found in the array.

 

[Ron Rosenfeld]

Responding to your points:-

1. Numbers have now be converted to text.

2. If multiple matches - This should not occur as each
value in the array is unique. The value being looked up
is 98% likely to have more digits. If by chance a muliple
match does occur a #NA should be returned, as would be
the case if no match was found.

3. Yes, all values in the lookup array need to be
considered.


Below are the values to be looked up in the array

Column A
2259680
3528900095
3548800090
46548903696
48235489097
666902195699
790295995994
8529898999942
99898994694531
9997191234899352
9998321646413185


Below is a sample of the array (MGT)

Column A Column B Column C
2259 UZBWLS Wesless, Uzbekistan
3528 USALLG Local Line Gate, USA
35488 MRTNLT Network line Trunk, Mauritius
465489 NPLNET Net Pro Ltd, Nepal
482354 STATRL Tralin, Stasbourg
66690 HOLIDY Idye, Holland
7902 BTGPLS Pleasure, British Territory
8529 PRYRIS T-Mobile, Priory
998 BLROIT T-Mobile, Belarus
9997 GRAPTT PTT, Greenland
9998 GRAPTT PTT, Greenland
9999 GRAATC Advanced Transp. Co., Greenland

As mentioned above, in the vast majority of cases, the
number being looked up will have more digits than that
being found in the array.

Well, here's a first shot at a UDF (user defined function) to accomplish what
you describe.

You'll need to do some testing to work out any bugs, as I'm not sure of all the
implications.

To enter this:
<alt><F11> opens the VB Editor
Ensure your project is highlighted in the Project Explorer window, then
Insert/Module and paste in the code below.

To use this UDF, enter the formula:

=LookupSpecial(lookup_value, lookup_arr, Col)

Let's see what happens on real data!


--ron

 

[Ron Rosenfeld]

Well, here's a first shot at a UDF (user defined function) to accomplish what
you describe.

You'll need to do some testing to work out any bugs, as I'm not sure of all the
implications.

To enter this:
<alt><F11> opens the VB Editor
Ensure your project is highlighted in the Project Explorer window, then
Insert/Module and paste in the code below.

To use this UDF, enter the formula:

=LookupSpecial(lookup_value, lookup_arr, Col)

Let's see what happens on real data!


--ron

Hmmm, the Function seems to be missing. I don't know what happened. I worked
a while on it and apparently forgot to copy it into the message before posting.
I'll see if I can recreate it.


--ron

 

[Ron Rosenfeld]

Well, here's a first shot at a UDF (user defined function) to accomplish what
you describe.

You'll need to do some testing to work out any bugs, as I'm not sure of all the
implications.

To enter this:
<alt><F11> opens the VB Editor
Ensure your project is highlighted in the Project Explorer window, then
Insert/Module and paste in the code below.

To use this UDF, enter the formula:

=LookupSpecial(lookup_value, lookup_arr, Col)

Let's see what happens on real data!


--ron

OK, Here's the reconstructed UDF.

But test it to ensure it does what you want:

=========================
Option Explicit

Function LookupSpecial(lookup_value As String, lookup_arr As Range, col As
Integer) As String
Dim i As Integer
Dim c As Range
Dim LookupStrings()
Dim Flag As Integer
Dim Match As Range

ReDim LookupStrings(Len(lookup_value) - 1)

For i = 0 To UBound(LookupStrings)
LookupStrings(i) = Mid(lookup_value, 1, i + 1)
Next i

For i = UBound(LookupStrings) To 0 Step -1
For Each c In lookup_arr
If c.Text Like LookupStrings(i) & "*" Then
Set Match = c
Flag = Flag + 1
End If
Next c
If Not Match Is Nothing Then Exit For
Next i

If Flag = 0 Then LookupSpecial = "No Match"
If Flag > 1 Then LookupSpecial = "Multiple Matches"
If Flag = 1 Then LookupSpecial = Match.Offset(0, col - 1).Text

End Function
=================================


--ron

 

[Yandros]

-----Original Message-----


Hmmm, the Function seems to be missing. I don't know what happened. I worked
a while on it and apparently forgot to copy it into the message before posting.
I'll see if I can recreate it.


--ron
.

Thanks for putting so much of your time and effort into
this. I'll check back later for your posting.

Yandros