Help on VLOOKUP

Hi assaf1978,
Thanks for ur response, I've tried using FALSE as argument, I've also tried
using combination of Index/Match functions but they are still giving wrong
results 'cos the numers are being compared numerically, rather than matching
the first few digits of Col F with values in Col C. Is there any function or
combination of functions that can be used. Contributions are highly
appreciated.

Thanks.
Sunez

"assaf1978" wrote:

> The only thing I can think of is to put true at the end of the vlookup and
> not false. I think it goes from the beginning but not entirely sure
>
> "Sunez" wrote:
>
> > Hi,
> > My lookup_values in Col F have more number of digits than that of the first
> > column in the table_array (C$2$8254). How can I do a partial matching such
> > that if a value in Col F begins with one or more values in Col C, then the
> > corresponding value in Col D to the highest of those matching(partial) values
> > is returned. E.g
> >
> > Col C Col D Col F
> > 1 9645 0.23 964455533
> > 2 9644 0.31 9685768961
> > 3 964 0.21 92347556
> > 4 96 0.40 8593646462
> > 5 95843 0.22 9683034746
> > 6 9583 0.14 424567899
> > .
> > .
> > If 964455533 (F1) is looked up in Col C, then 0.31(D2) should be returned
> > since 9644(C2) is the highest number in Col C that starts 964455533, but if
> > 9685768961(F2) is looked up, then 0.40(D4) should be returned and so on. Can
> > VLOOKUP be used or is there any other way? I really need your help.
> >
> > Thanks in advance.
> > Sunez

Could your table be sorted by length of column C? So that you have all
the 2-digit numbers together, then the 3-digit numbers, then 4-digit
etc.

If so, then I have done this with telephone numbers, matching on the
largest number of digits up to 10 in order to get the call type -
looks like a very similar situation to yours. Once the data is sorted
you then set up named ranges for each digit-length, so I had L_1, L_2,
L_3 etc, and then a massive formula which basically relied on INDEX
and MAX(of several MATCH functions, each looking at a different named
range and number of characters using LEFT).

It was some years ago that I did this, so I will have to look for the
files, so let me know if you are interested in this approach.

Pete

On Aug 27, 5:12 pm, Sunez <Su....DeleteThis@discussions.microsoft.com> wrote:
> Hi assaf1978,
> Thanks for ur response, I've tried using FALSE as argument, I've also tried
> using combination of Index/Match functions but they are still giving wrong
> results 'cos the numers are being compared numerically, rather than matching
> the first few digits of Col F with values in Col C. Is there any function or
> combination of functions that can be used. Contributions are highly
> appreciated.
>
> Thanks.
> Sunez
>
>
>
> "assaf1978" wrote:
> > The only thing I can think of is to put true at the end of the vlookup and
> > not false. I think it goes from the beginning but not entirely sure
>
> > "Sunez" wrote:
>
> > > Hi,
> > > My lookup_values in Col F have more number of digits than that of the first
> > > column in the table_array (C$2$8254). How can I do a partial matching such
> > > that if a value in Col F begins with one or more values in Col C, then the
> > > corresponding value in Col D to the highest of those matching(partial) values
> > > is returned. E.g
>
> > >           Col C     Col D        Col F
> > >      1    9645      0.23        964455533
> > >      2    9644      0.31        9685768961
> > >      3    964        0.21        92347556
> > >      4    96          0.40        8593646462
> > >      5    95843     0.22        9683034746
> > >      6    9583       0.14        424567899
> > >       .
> > >       .
> > > If 964455533 (F1) is looked up in Col C, then 0.31(D2) should be returned
> > > since 9644(C2) is the highest number in Col C that starts 964455533, but if
> > > 9685768961(F2) is looked up, then 0.40(D4) should be returned and so on. Can
> > > VLOOKUP be used or is there any other way? I really need your help.
>
> > > Thanks in advance.
> > > Sunez- Hide quoted text -
>
> - Show quoted text -

Hello Pete_UK,
They can be sorted just as u suggested and I don't mind any approach used. I
just want it done. I'll really appreciate ur help.

Thanks,
Sunez

"Pete_UK" wrote:

> Could your table be sorted by length of column C? So that you have all
> the 2-digit numbers together, then the 3-digit numbers, then 4-digit
> etc.
>
> If so, then I have done this with telephone numbers, matching on the
> largest number of digits up to 10 in order to get the call type -
> looks like a very similar situation to yours. Once the data is sorted
> you then set up named ranges for each digit-length, so I had L_1, L_2,
> L_3 etc, and then a massive formula which basically relied on INDEX
> and MAX(of several MATCH functions, each looking at a different named
> range and number of characters using LEFT).
>
> It was some years ago that I did this, so I will have to look for the
> files, so let me know if you are interested in this approach.
>
> Pete
>
> On Aug 27, 5:12 pm, Sunez <Su... RemoveThis @discussions.microsoft.com> wrote:
> > Hi assaf1978,
> > Thanks for ur response, I've tried using FALSE as argument, I've also tried
> > using combination of Index/Match functions but they are still giving wrong
> > results 'cos the numers are being compared numerically, rather than matching
> > the first few digits of Col F with values in Col C. Is there any function or
> > combination of functions that can be used. Contributions are highly
> > appreciated.
> >
> > Thanks.
> > Sunez
> >
> >
> >
> > "assaf1978" wrote:
> > > The only thing I can think of is to put true at the end of the vlookup and
> > > not false. I think it goes from the beginning but not entirely sure
> >
> > > "Sunez" wrote:
> >
> > > > Hi,
> > > > My lookup_values in Col F have more number of digits than that of the first
> > > > column in the table_array (C$2$8254). How can I do a partial matching such
> > > > that if a value in Col F begins with one or more values in Col C, then the
> > > > corresponding value in Col D to the highest of those matching(partial) values
> > > > is returned. E.g
> >
> > > > Col C Col D Col F
> > > > 1 9645 0.23 964455533
> > > > 2 9644 0.31 9685768961
> > > > 3 964 0.21 92347556
> > > > 4 96 0.40 8593646462
> > > > 5 95843 0.22 9683034746
> > > > 6 9583 0.14 424567899
> > > > .
> > > > .
> > > > If 964455533 (F1) is looked up in Col C, then 0.31(D2) should be returned
> > > > since 9644(C2) is the highest number in Col C that starts 964455533, but if
> > > > 9685768961(F2) is looked up, then 0.40(D4) should be returned and so on. Can
> > > > VLOOKUP be used or is there any other way? I really need your help.
> >
> > > > Thanks in advance.
> > > > Sunez- Hide quoted text -
> >
> > - Show quoted text -
>
>

Okay, I'm just about to go out now, so I'll pick it up later on. Can
you tell me the range of digit-length you have in column C - your
example only shows up to 5 digits.

Also, would it be better to have your table in a separate sheet from
the data in column F, and if so, would this mean changes to the
columns where the data is?

Pete

On Aug 27, 6:18 pm, Sunez <Su....DeleteThis@discussions.microsoft.com> wrote:
> Hello Pete_UK,
> They can be sorted just as u suggested and I don't mind any approach used.. I
> just want it done. I'll really appreciate ur help.
>
> Thanks,
> Sunez
>
>
>
> "Pete_UK" wrote:
> > Could your table be sorted by length of column C? So that you have all
> > the 2-digit numbers together, then the 3-digit numbers, then 4-digit
> > etc.
>
> > If so, then I have done this with telephone numbers, matching on the
> > largest number of digits up to 10 in order to get the call type -
> > looks like a very similar situation to yours. Once the data is sorted
> > you then set up named ranges for each digit-length, so I had L_1, L_2,
> > L_3 etc, and then a massive formula which basically relied on INDEX
> > and MAX(of several MATCH functions, each looking at a different named
> > range and number of characters using LEFT).
>
> > It was some years ago that I did this, so I will have to look for the
> > files, so let me know if you are interested in this approach.
>
> > Pete
>
> > On Aug 27, 5:12 pm, Sunez <Su....DeleteThis@discussions.microsoft.com> wrote:
> > > Hi assaf1978,
> > > Thanks for ur response, I've tried using FALSE as argument, I've also tried
> > > using combination of Index/Match functions but they are still giving wrong
> > > results 'cos the numers are being compared numerically, rather than matching
> > > the first few digits of Col F with values in Col C. Is there any function or
> > > combination of functions that can be used. Contributions are highly
> > > appreciated.
>
> > > Thanks.
> > > Sunez
>
> > > "assaf1978" wrote:
> > > > The only thing I can think of is to put true at the end of the vlookup and
> > > > not false. I think it goes from the beginning but not entirely sure
>
> > > > "Sunez" wrote:
>
> > > > > Hi,
> > > > > My lookup_values in Col F have more number of digits than that of the first
> > > > > column in the table_array (C$2$8254). How can I do a partial matching such
> > > > > that if a value in Col F begins with one or more values in Col C, then the
> > > > > corresponding value in Col D to the highest of those matching(partial) values
> > > > > is returned. E.g
>
> > > > >           Col C     Col D        Col F
> > > > >      1    9645      0.23        964455533
> > > > >      2    9644      0.31        9685768961
> > > > >      3    964        0.21        92347556
> > > > >      4    96          0.40        8593646462
> > > > >      5    95843     0.22        9683034746
> > > > >      6    9583       0.14        424567899
> > > > >       .
> > > > >       .
> > > > > If 964455533 (F1) is looked up in Col C, then 0.31(D2) should be returned
> > > > > since 9644(C2) is the highest number in Col C that starts 964455533, but if
> > > > > 9685768961(F2) is looked up, then 0.40(D4) should be returned and so on. Can
> > > > > VLOOKUP be used or is there any other way? I really need your help.
>
> > > > > Thanks in advance.
> > > > > Sunez- Hide quoted text -
>
> > > - Show quoted text -- Hide quoted text -
>
> - Show quoted text -

I've got the formula working on your test data, but this is a bit limited.
Can you post a longer example, with about 20 rows?

Also, I need the earlier questions answered - i.e. the range of the number
of digits in column C, and do you want your table on a separate sheet?

Pete

"Pete_UK" <pashurst.DeleteThis@auditel.net> wrote in message
news:663de1b1-9e7a-4027-bac0-8326f8db2b0a@d1g2000hsg.googlegroups.com...
Okay, I'm just about to go out now, so I'll pick it up later on. Can
you tell me the range of digit-length you have in column C - your
example only shows up to 5 digits.

Also, would it be better to have your table in a separate sheet from
the data in column F, and if so, would this mean changes to the
columns where the data is?

Pete

On Aug 27, 6:18 pm, Sunez <Su....DeleteThis@discussions.microsoft.com> wrote:
> Hello Pete_UK,
> They can be sorted just as u suggested and I don't mind any approach used.
> I
> just want it done. I'll really appreciate ur help.
>
> Thanks,
> Sunez
>
>
>
> "Pete_UK" wrote:
> > Could your table be sorted by length of column C? So that you have all
> > the 2-digit numbers together, then the 3-digit numbers, then 4-digit
> > etc.
>
> > If so, then I have done this with telephone numbers, matching on the
> > largest number of digits up to 10 in order to get the call type -
> > looks like a very similar situation to yours. Once the data is sorted
> > you then set up named ranges for each digit-length, so I had L_1, L_2,
> > L_3 etc, and then a massive formula which basically relied on INDEX
> > and MAX(of several MATCH functions, each looking at a different named
> > range and number of characters using LEFT).
>
> > It was some years ago that I did this, so I will have to look for the
> > files, so let me know if you are interested in this approach.
>
> > Pete
>
> > On Aug 27, 5:12 pm, Sunez <Su....DeleteThis@discussions.microsoft.com> wrote:
> > > Hi assaf1978,
> > > Thanks for ur response, I've tried using FALSE as argument, I've also
> > > tried
> > > using combination of Index/Match functions but they are still giving
> > > wrong
> > > results 'cos the numers are being compared numerically, rather than
> > > matching
> > > the first few digits of Col F with values in Col C. Is there any
> > > function or
> > > combination of functions that can be used. Contributions are highly
> > > appreciated.
>
> > > Thanks.
> > > Sunez
>
> > > "assaf1978" wrote:
> > > > The only thing I can think of is to put true at the end of the
> > > > vlookup and
> > > > not false. I think it goes from the beginning but not entirely sure
>
> > > > "Sunez" wrote:
>
> > > > > Hi,
> > > > > My lookup_values in Col F have more number of digits than that of
> > > > > the first
> > > > > column in the table_array (C$2$8254). How can I do a partial
> > > > > matching such
> > > > > that if a value in Col F begins with one or more values in Col C,
> > > > > then the
> > > > > corresponding value in Col D to the highest of those
> > > > > matching(partial) values
> > > > > is returned. E.g
>
> > > > > Col C Col D Col F
> > > > > 1 9645 0.23 964455533
> > > > > 2 9644 0.31 9685768961
> > > > > 3 964 0.21 92347556
> > > > > 4 96 0.40 8593646462
> > > > > 5 95843 0.22 9683034746
> > > > > 6 9583 0.14 424567899
> > > > > .
> > > > > .
> > > > > If 964455533 (F1) is looked up in Col C, then 0.31(D2) should be
> > > > > returned
> > > > > since 9644(C2) is the highest number in Col C that starts
> > > > > 964455533, but if
> > > > > 9685768961(F2) is looked up, then 0.40(D4) should be returned and
> > > > > so on. Can
> > > > > VLOOKUP be used or is there any other way? I really need your
> > > > > help.
>
> > > > > Thanks in advance.
> > > > > Sunez- Hide quoted text -
>
> > > - Show quoted text -- Hide quoted text -
>
> - Show quoted text -

Hi,
Thanks for ur response. The values in Col C ranges from 1-digit to 9-digits.
I want Col F to be on separate sheet since it's values changes while Col C &
D remains constant, so Col F is going to be on a separate sheet. Here's a
longer example with Col C & D sorted in ascending order:

Row Col C Col D Col F
1 7 0.04 279874857
2 20 0.13 482244288
3 27 0.05 947343425
4 34 0.01 124623028429
5 94 0.15 4428473839
6 95 0.25 99899292340
7 202 0.13 743427633
8 212 0.14 1124859574
9 216 0.16 1250343425
10 996 0.09 7733222553
11 998 0.06 2030294021
12 1204 0.01 484312594
13 1226 0.15 4034885556
14 4822 0.01 1238495345
15 4850 0.18 4585547
16 4860 0.11 12463593
17 1242 0.03 6981111
18 1246 0.08 2341223
19 1250 0.01 12954745
20 99898 0.22 18246512597
21 99899 0.07 95853089
22 124623 0.15 48607809655
23 124626 0.18 482242322097

Thanks in advance.
Sunez

"Pete_UK" wrote:

> I've got the formula working on your test data, but this is a bit limited.
> Can you post a longer example, with about 20 rows?
>
> Also, I need the earlier questions answered - i.e. the range of the number
> of digits in column C, and do you want your table on a separate sheet?
>
> Pete
>
> "Pete_UK" <pashurst.DeleteThis@auditel.net> wrote in message
> news:663de1b1-9e7a-4027-bac0-8326f8db2b0a@d1g2000hsg.googlegroups.com...
> Okay, I'm just about to go out now, so I'll pick it up later on. Can
> you tell me the range of digit-length you have in column C - your
> example only shows up to 5 digits.
>
> Also, would it be better to have your table in a separate sheet from
> the data in column F, and if so, would this mean changes to the
> columns where the data is?
>
> Pete
>
> On Aug 27, 6:18 pm, Sunez <Su....DeleteThis@discussions.microsoft.com> wrote:
> > Hello Pete_UK,
> > They can be sorted just as u suggested and I don't mind any approach used.
> > I
> > just want it done. I'll really appreciate ur help.
> >
> > Thanks,
> > Sunez
> >
> >
> >
> > "Pete_UK" wrote:
> > > Could your table be sorted by length of column C? So that you have all
> > > the 2-digit numbers together, then the 3-digit numbers, then 4-digit
> > > etc.
> >
> > > If so, then I have done this with telephone numbers, matching on the
> > > largest number of digits up to 10 in order to get the call type -
> > > looks like a very similar situation to yours. Once the data is sorted
> > > you then set up named ranges for each digit-length, so I had L_1, L_2,
> > > L_3 etc, and then a massive formula which basically relied on INDEX
> > > and MAX(of several MATCH functions, each looking at a different named
> > > range and number of characters using LEFT).
> >
> > > It was some years ago that I did this, so I will have to look for the
> > > files, so let me know if you are interested in this approach.
> >
> > > Pete
> >
> > > On Aug 27, 5:12 pm, Sunez <Su....DeleteThis@discussions.microsoft.com> wrote:
> > > > Hi assaf1978,
> > > > Thanks for ur response, I've tried using FALSE as argument, I've also
> > > > tried
> > > > using combination of Index/Match functions but they are still giving
> > > > wrong
> > > > results 'cos the numers are being compared numerically, rather than
> > > > matching
> > > > the first few digits of Col F with values in Col C. Is there any
> > > > function or
> > > > combination of functions that can be used. Contributions are highly
> > > > appreciated.
> >
> > > > Thanks.
> > > > Sunez
> >
> > > > "assaf1978" wrote:
> > > > > The only thing I can think of is to put true at the end of the
> > > > > vlookup and
> > > > > not false. I think it goes from the beginning but not entirely sure
> >
> > > > > "Sunez" wrote:
> >
> > > > > > Hi,
> > > > > > My lookup_values in Col F have more number of digits than that of
> > > > > > the first
> > > > > > column in the table_array (C$2$8254). How can I do a partial
> > > > > > matching such
> > > > > > that if a value in Col F begins with one or more values in Col C,
> > > > > > then the
> > > > > > corresponding value in Col D to the highest of those
> > > > > > matching(partial) values
> > > > > > is returned. E.g
> >
> > > > > > Col C Col D Col F
> > > > > > 1 9645 0.23 964455533
> > > > > > 2 9644 0.31 9685768961
> > > > > > 3 964 0.21 92347556
> > > > > > 4 96 0.40 8593646462
> > > > > > 5 95843 0.22 9683034746
> > > > > > 6 9583 0.14 424567899
> > > > > > .
> > > > > > .
> > > > > > If 964455533 (F1) is looked up in Col C, then 0.31(D2) should be
> > > > > > returned
> > > > > > since 9644(C2) is the highest number in Col C that starts
> > > > > > 964455533, but if
> > > > > > 9685768961(F2) is looked up, then 0.40(D4) should be returned and
> > > > > > so on. Can
> > > > > > VLOOKUP be used or is there any other way? I really need your
> > > > > > help.
> >
> > > > > > Thanks in advance.
> > > > > > Sunez- Hide quoted text -
> >
> > > > - Show quoted text -- Hide quoted text -
> >
> > - Show quoted text -
>
>
>