Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
vlookup by the lengh
I know this is simple, but for the life of me I cannot figure it out your
help would be greatly appreciated!! I need to create a vlookup that is based not only on the exact characters of the Lookup_Value field, but also off the length. For example, I have a list of general part numbers in file1, and a list of parts with extensions and the parts details in file2. Using the example below, I would like to lookup on the part in file1, and based on the characters and length of the part search only for an exact match for that part in File2 . In this case for the first part, the lookup function would only look at the first 6 characters of the field before finding an exact match to ABC123; the lookup for the second part would look only at the first 7 characters. Using True in the Range_vlookup field for a closest match does not work. Since the part numbers vary in character length and extension length I cannot lookup a LEFT xxx on my parts in File2. File 1 File2 PART PART w extension Package ABC123 ABC123abcde BAG 14 DEF4567 DEF456712fg BAG 15 I hope I have explained this well enough for you to get an idea of what I need. Any help would be appreciated!! |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
vlookup by the lengh
Shairal wrote:
I know this is simple, but for the life of me I cannot figure it out your help would be greatly appreciated!! I need to create a vlookup that is based not only on the exact characters of the Lookup_Value field, but also off the length. For example, I have a list of general part numbers in file1, and a list of parts with extensions and the parts details in file2. Using the example below, I would like to lookup on the part in file1, and based on the characters and length of the part search only for an exact match for that part in File2 . In this case for the first part, the lookup function would only look at the first 6 characters of the field before finding an exact match to ABC123; the lookup for the second part would look only at the first 7 characters. Using True in the Range_vlookup field for a closest match does not work. Since the part numbers vary in character length and extension length I cannot lookup a LEFT xxx on my parts in File2. File 1 File2 PART PART w extension Package ABC123 ABC123abcde BAG 14 DEF4567 DEF456712fg BAG 15 I hope I have explained this well enough for you to get an idea of what I need. Any help would be appreciated!! Try this array* formula in File1 B2 and then fill down. Adjust the row bounds as needed to fully contain the rows in File2: =INDEX([File2.xls]Sheet1!$B$2:$B$3,MATCH($A2,LEFT([File2.xls]Sheet1!$A$2:$A$3,LEN($A2)))) *Commit an array formula by pressing Ctrl+Shift+Enter, do not just press Enter or Tab. P.S. If you copy & paste this formula, make sure both File1 and File2 are open for the external data references to be set appropriately. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
vlookup by the lengh
I put my data in a single workbook.
Sheet1 held my list (6 or 7 characters) Sheet2 held my table (long part #'s) and package field. =INDEX(Sheet2!B1:B100,MATCH(A2,LEFT(Sheet2!A1:A100 ,LEN(A2)),0)) This is an array formula. Hit ctrl-shift-enter instead of enter. If you do it correctly, excel will wrap curly brackets {} around your formula. (don't type them yourself.) Adjust the range to match--but you can only use the whole column in xl2007. Shairal wrote: I know this is simple, but for the life of me I cannot figure it out your help would be greatly appreciated!! I need to create a vlookup that is based not only on the exact characters of the Lookup_Value field, but also off the length. For example, I have a list of general part numbers in file1, and a list of parts with extensions and the parts details in file2. Using the example below, I would like to lookup on the part in file1, and based on the characters and length of the part search only for an exact match for that part in File2 . In this case for the first part, the lookup function would only look at the first 6 characters of the field before finding an exact match to ABC123; the lookup for the second part would look only at the first 7 characters. Using True in the Range_vlookup field for a closest match does not work. Since the part numbers vary in character length and extension length I cannot lookup a LEFT xxx on my parts in File2. File 1 File2 PART PART w extension Package ABC123 ABC123abcde BAG 14 DEF4567 DEF456712fg BAG 15 I hope I have explained this well enough for you to get an idea of what I need. Any help would be appreciated!! -- Dave Peterson |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
vlookup by the lengh
Thanks Dave, worked perfectly!
"Dave Peterson" wrote: I put my data in a single workbook. Sheet1 held my list (6 or 7 characters) Sheet2 held my table (long part #'s) and package field. =INDEX(Sheet2!B1:B100,MATCH(A2,LEFT(Sheet2!A1:A100 ,LEN(A2)),0)) This is an array formula. Hit ctrl-shift-enter instead of enter. If you do it correctly, excel will wrap curly brackets {} around your formula. (don't type them yourself.) Adjust the range to match--but you can only use the whole column in xl2007. Shairal wrote: I know this is simple, but for the life of me I cannot figure it out â your help would be greatly appreciated!! I need to create a âœvlookupâ that is based not only on the exact characters of the Lookup_Value field, but also off the length. For example, I have a list of general part numbers in file1, and a list of parts with extensions and the partâs details in file2. Using the example below, I would like to lookup on the part in file1, and based on the characters and length of the part search only for an exact match for that part in File2 . In this case for the first part, the lookup function would only look at the first 6 characters of the field before finding an exact match to ABC123; the lookup for the second part would look only at the first 7 characters. Using True in the Range_vlookup field for a âœclosest matchâ does not work. Since the part numbers vary in character length and extension length I cannot lookup a LEFT xxx on my parts in File2. File 1 File2 PART PART w extension Package ABC123 ABC123abcde BAG 14 DEF4567 DEF456712fg BAG 15 I hope I have explained this well enough for you to get an idea of what I need. Any help would be appreciated!! -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
VLookUp - Does the VLookUp return the exact information? | Excel Worksheet Functions | |||
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 | |||
Vlookup info being used without vlookup table attached? | Excel Worksheet Functions |