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