Vlookup variation

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

The fact that you're asking about VLookup, combined with your sample code,
leads me to believe you're dealing with Excel. I'd suggest asking your
question in a newsgroup related to Excel. (There are lots starting
microsoft.public.excel)

This newsgroup is for questions about queries in Access, the database
product that's part of Office Professional.

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)



"Yandros" wrote in message
...
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

Douglas,

You are indeed correct. I realised my mistake about 5
minutes after posting the message.

I have subsequently posted it in the excel newsgroup.

Thanks for taking the time to respond.

Yandros

-----Original Message-----
The fact that you're asking about VLookup, combined with
your sample code,
leads me to believe you're dealing with Excel. I'd
suggest asking your
question in a newsgroup related to Excel. (There are
lots starting
microsoft.public.excel)

This newsgroup is for questions about queries in Access,
the database
product that's part of Office Professional.

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)



"Yandros" wrote in
message
...
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


.