Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Help on VLOOKUP
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Help on VLOOKUP
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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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: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 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Help on VLOOKUP
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 - |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Help on VLOOKUP
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 - |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Help on VLOOKUP
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 - |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Help on VLOOKUP
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 - |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
Help on VLOOKUP
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 - |
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
Help on VLOOKUP
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 C1:D31. (More after lunch) Pete On Aug 28, 9:34*am, Sunez wrote: 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 -- Hide quoted text - - Show quoted text - |
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
Help on VLOOKUP
Alright.
"Pete_UK" wrote: 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 C1:D31. (More after lunch) Pete On Aug 28, 9:34 am, Sunez wrote: 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 -- Hide quoted text - - Show quoted text - |
#11
Posted to microsoft.public.excel.misc
|
|||
|
|||
Help on VLOOKUP
(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 C1:D31 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,v alues,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 On Aug 28, 12:00*pm, Pete_UK wrote: 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 C1:D31. (More after lunch) Pete On Aug 28, 9:34*am, Sunez wrote: 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 -- Hide quoted text - - Show quoted text -- Hide quoted text - - Show quoted text - |
#12
Posted to microsoft.public.excel.misc
|
|||
|
|||
Help on VLOOKUP
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(M ATCH(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,value s,0),2) Pete, I really appreciate your efforts. Sunez "Pete_UK" wrote: (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 C1:D31 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,v alues,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 On Aug 28, 12:00 pm, Pete_UK wrote: 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 C1:D31. (More after lunch) Pete On Aug 28, 9:34 am, Sunez wrote: 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 -- Hide quoted text - - Show quoted text -- Hide quoted text - - Show quoted text - |
#13
Posted to microsoft.public.excel.misc
|
|||
|
|||
Help on VLOOKUP
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 On Aug 28, 5:22*pm, Sunez wrote: 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(I*SNA(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(LE FT(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 |
#14
Posted to microsoft.public.excel.misc
|
|||
|
|||
Help on VLOOKUP
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" wrote: 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 On Aug 28, 5:22 pm, Sunez wrote: 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(IÂ*SNA(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(IS NA(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 |
#15
Posted to microsoft.public.excel.misc
|
|||
|
|||
Help on VLOOKUP
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 On Aug 28, 8:30*pm, Sunez wrote: 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" wrote: 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 On Aug 28, 5:22 pm, Sunez wrote: 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(I**SNA(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(IS NA(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(F*1,9)**1,L_9,0)),0,9) ))*1,values,0),2) Pete, I really appreciate your efforts. Sunez- Hide quoted text - - Show quoted text - |
#16
Posted to microsoft.public.excel.misc
|
|||
|
|||
Help on VLOOKUP
Hi,
Thanks for the enlightenment. Pete, I think u deserve an MVP award.... Many thanks to you assaf1978 for ur contribution. Sunez "Pete_UK" wrote: 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 On Aug 28, 8:30 pm, Sunez wrote: 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" wrote: 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 On Aug 28, 5:22 pm, Sunez wrote: 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(IÂ*Â*SNA(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),I F(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(LEF T(F1,7)*1Â*,L_Â*7,0)),0,7),IF(ISNA(MATCH(LEFT(F1,8 )*1,L_8,0)),0,8),IF(ISNA(MATCH(LEFT(FÂ*1,9)Â**1,L_ 9,0)),0,9)))*1,values,0),2) Pete, I really appreciate your efforts. Sunez- Hide quoted text - - Show quoted text - |
#17
Posted to microsoft.public.excel.misc
|
|||
|
|||
Help on VLOOKUP
You're welcome, Sunez - thanks for your kind words.
Pete On Aug 29, 4:25*pm, Sunez wrote: Hi, Thanks for the enlightenment. Pete, I think u deserve an MVP award.... Many thanks to you assaf1978 for ur contribution. Sunez |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Vlookup in vlookup - taking the result as array name | Excel Worksheet Functions | |||
Combine VLOOKUP and IF function so #NA isn't returned as a value from VLOOKUP | Excel Discussion (Misc queries) | |||
Vlookup -=VLOOKUP(F9,LookUp1!$A$2:$B$1504,2,FALSE) | New Users to Excel | |||
IF(AND(val1=VLOOKUP( );val2>=VLOOKUP( );val2<=VLOOKUP( );VLOOKUP( | Excel Worksheet Functions | |||
Vlookup info being used without vlookup table attached? | Excel Worksheet Functions |