vlookup by the lengh

[Shairal]

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!!

 

[smartin]

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!!

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.

 

[Dave Peterson]

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]

Thanks Dave, worked perfectly!

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.