Hi,
My lookup_values in Col F have more number of digits than that of the first
column in the table_array (C$2:D$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

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:D$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

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:D$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 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:D$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 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:D$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 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 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:D$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 -