Re: Vlookup variation
From: Yandros (anonymous_at_discussions.microsoft.com)
Date: 06/20/04
- Next message: genzu: "MATCH function with text - confused"
- Previous message: Alan Beban: "Re: Match & Index Combined Row & Multiple extractions"
- In reply to: CLR: "Re: Vlookup variation"
- Messages sorted by: [ date ] [ thread ]
Date: Sun, 20 Jun 2004 14:27:03 -0700
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
>-----Original Message-----
>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" <anonymous@discussions.microsoft.com> wrote in
message
>news:1eb5001c456ea$e29fdc80$a301280a@phx.gbl...
>> 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
>>
>
>
>.
>
- Next message: genzu: "MATCH function with text - confused"
- Previous message: Alan Beban: "Re: Match & Index Combined Row & Multiple extractions"
- In reply to: CLR: "Re: Vlookup variation"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
