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!! |
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. |
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 |
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 |
All times are GMT +1. The time now is 02:05 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com