Help on VLOOKUP

[Sunez]

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

 

[assaf1978]

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]

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

 

[Pete_UK]

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

 

[Sunez]

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]

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

 

[Pete_UK]

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

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

 

[Sunez]

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]

First of all, I would suggest that you have every single-digit number
in your table - that way there will always be a match. Consequently, I
have amended your table like this:

1 x
2 x
3 x
4 x
5 x
6 x
7 0.04
8 x
9 x
20 0.13
27 0.05
34 0.01
94 0.15
95 0.25
202 0.13
212 0.14
216 0.16
996 0.09
998 0.06
1204 0.01
1226 0.15
4822 0.01
4850 0.18
4860 0.11
1242 0.03
1246 0.08
1250 0.01
99898 0.22
99899 0.07
124623 0.15
124626 0.18

You can put whatever values you like for x. I have put this in Sheet1,
so that it occupies C131.

(More after lunch)

Pete

 

[Sunez]

Alright.

First of all, I would suggest that you have every single-digit number
in your table - that way there will always be a match. Consequently, I
have amended your table like this:

1 x
2 x
3 x
4 x
5 x
6 x
7 0.04
8 x
9 x
20 0.13
27 0.05
34 0.01
94 0.15
95 0.25
202 0.13
212 0.14
216 0.16
996 0.09
998 0.06
1204 0.01
1226 0.15
4822 0.01
4850 0.18
4860 0.11
1242 0.03
1246 0.08
1250 0.01
99898 0.22
99899 0.07
124623 0.15
124626 0.18

You can put whatever values you like for x. I have put this in Sheet1,
so that it occupies C131.

(More after lunch)

Pete

 

[Pete_UK]

(Suitably refreshed now ...)

Next you need to set up some named ranges. This will make the formula
which I will give you shorter, and hopefully easier to follow.

Highlight all the data in the table (in this case C131 on Sheet1)
and click on Insert | Name | Define and give this the name
"Table" (without the quotes) in the top box, then click OK. Then
highlight all the data in column C (i.e. C1:C31) and Insert | Name |
Define again, and this time give the name "values" (no quotes).

Now you need to set up a name for each of the block of numbers in
column C that are the same number of digits. So highlight C1:C9 and
give this the name "L_1" (again, without the quotes). The next range
will be C10:C14, and give this the name L_2 (for 2-digit numbers).
Similarly for C15:C19 (L_3), C20:C27 (L_4), C28:C29 (L_5) and C30:C31
(L_6).

Obviously in your real table the ranges will be different, but I am
assuming that you will want to test out my solution on your example
data first before applying it to the actual data.

Now, with the list of numbers you want to find partial matches on
located in column F of Sheet2 (starting in F1), put this formula in an
adjacent cell:

=INDEX(Table,MATCH(LEFT(F1,MAX(IF(ISNA(MATCH(LEFT(F1,1)*1,L_1,0)),
0,1),IF(ISNA(MATCH(LEFT(F1,2)*1,L_2,0)),
0,2),IF(ISNA(MATCH(LEFT(F1,3)*1,L_3,0)),
0,3),IF(ISNA(MATCH(LEFT(F1,4)*1,L_4,0)),
0,4),IF(ISNA(MATCH(LEFT(F1,5)*1,L_5,0)),
0,5),IF(ISNA(MATCH(LEFT(F1,6)*1,L_6,0)),0,6)))*1,values,0),2)

Note that this is all one formula - be wary of spurious line-breaks
that your newsreader might insert. Copy this down the column for as
many values as you have. I got these results for the 23 numbers in
your second example:

279874857 0.05
482244288 0.01
947343425 0.15
124623028429 0.15
4428473839 x
99899292340 0.07
743427633 0.04
1124859574 x
1250343425 0.01
7733222553 0.04
2030294021 0.13
484312594 x
4034885556 x
1238495345 x
4585547 x
12463593 0.08
6981111 x
2341223 x
12954745 x
18246512597 x
95853089 0.25
48607809655 0.11
482242322097 0.01

Try this out on your sample data, then post back if you need help
applying it to up to 9-digit numbers in your real data.

Hope this helps.

Pete

 

[Sunez]

Thanks Pete. Your formular works very well on the example I gave but was
giving #NAME? error thoughout when I tried it on actual data. Just trying to
figure out what could be the cause. Take a look at the formula in case I made
a mistake somewhere, I modified it to accomodate other number of digits.

=INDEX(Table,MATCH(LEFT(F1,MAX(IF(ISNA(MATCH(LEFT(F1,1)*1,L_1,0)),0,1),IF(ISNA(MATCH(LEFT(F1,2)*1,L_2,0)),0,2),IF(ISNA(MATCH(LEFT(F1,3)*1,L_3,0)),0,3),IF(ISNA(MATCH(LEFT(F1,4)*1,L_4,0)),0,4),IF(ISNA(MATCH(LEFT(F1,5)*1,L_5,0)),0,5),IF(ISNA(MATCH(LEFT(F1,6)*1,L_6,0)),0,6),IF(ISNA(MATCH(LEFT(F1,7)*1,L_7,0)),0,7),IF(ISNA(MATCH(LEFT(F1,8)*1,L_8,0)),0,8),IF(ISNA(MATCH(LEFT(F1,9)*1,L_9,0)),0,9)))*1,values,0),2)

Pete, I really appreciate your efforts.

Sunez

 

[Pete_UK]

Did you remember to set up named ranges for L_7, L_8 and L_9 in your
actual data table? Obviously, these relate to 7-, 8- and 9-digit
numbers. #NAME? means that Excel does not recognise the name of a
function or named range.

Your amendments to the formula seem to be okay.

Pete

 

[Sunez]

Hi Pete,
I'm sorry, I forgot to specify the name "values" for the column. The
formular works great! Thumb up for you, Pete. You are indeed a genius.

I'd like to know function of "*" in the formular. Thanks a million, I'm very
grateful.

Sunez

 

[Pete_UK]

Well, I'm glad you got it working - thanks for feeding back.

If you take LEFT of some number then the function returns a text
value, and this cannot be compared directly with the numbers in column
C. Hence each LEFT function is multiplied by 1 to convert it back into
a number. If your numbers in C column were in fact text values, then
you would not need any of the *1 parts (In my original application
dealing with phone numbers the numbers were all text values, as they
began with at least one leading zero, so I didn't need the *1).

Another way of dealing with this conversion would be to have a double
unary minus in front of each LEFT, i.e. --LEFT(...

Anyway, glad to be of help - I think this is one of my most complex
formulae.

Pete

 

[Sunez]

Hi,
Thanks for the enlightenment. Pete, I think u deserve an MVP award.... Many
thanks to you assaf1978 for ur contribution.

Sunez

 

[Pete_UK]

You're welcome, Sunez - thanks for your kind words.

Pete