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.