Look up part of a number within a serial number and cpy back assoc

[Seantastic]

I have a spreadsheet with 2 tabs.

First tab is named “inventoryâ€
In column “H†the are serial numbers in the following formats
ABBC.1234567
ABBC.1234567_RM
ABBC.1234567_FT
The only part of the serial number that interests me is the middle numbers
(i.e. 1234567)

In column “A†I have a list of “part codeâ€

In my second tab called “partsâ€
In column E there is a list of the middle numbers found in the first tab
(i.e. 1234567)

In my I would like to look up the number in Column “E†from sheet “partsâ€
(my second tab) that is found somewhere in column “H†in the middle of the
serial number in sheet “inventoryâ€, get the associated “part code†from
column “A†and copy it back into sheet “parts†(my second tab) in column “Lâ€

Sheet “inventoryâ€
A H
123 ABBC.1234467
456 ABBC.1234547_RM
897 ABBC.1234564_FT

Sheet “partsâ€
E L
1234547 456
1234564 897
1234467 123

 

[Pete_UK]

Put this in L1 of your parts sheet:

=INDEX(inventory!A:A,MATCH("*"&E1&"*",inventory!H:H,0))

and copy down as required.

Hope this helps.

Pete

 

[Mike H]

Try this

=VLOOKUP(--MID(H1,MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},H1&"0123456789")),SUMPRODUCT(LEN(H1)-LEN(SUBSTITUTE(H1,{0,1,2,3,4,5,6,7,8,9},"")))),Parts!E1:L36,8,FALSE)

Mike

 

[ShaneDevenshire]

Hi,

you can extract the middle part in a formula with =MID(A1,6,7) if all the
enteries are consistant. This will return a text entry, if you need it as a
number then
=--MID(A1,6,7)

Then use VLOOKUP against this column

 

[Seantastic]

Mike, does it mater if my serial numer are random? I put 123456 as an example
put they can be 1245978 or 6853247 etc.. any combination.

 

[Seantastic]

I get an error

Put this in L1 of your parts sheet:

=INDEX(inventory!A:A,MATCH("*"&E1&"*",inventory!H:H,0))

and copy down as required.

Hope this helps.

Pete

 

[Seantastic]

Pete can explain what each part of your formula does? I'm a novice to this
and when a copied and pasted it into the L1 cell it says " The formula you
type contains an error" and it highlights A,MATCH.

I am using excel 2003 - does that change anything?

 

[Pete_UK]

I didn't know how many entries you had in the inventory sheet, so I
used full column references. The MATCH part of the formula is using
wildcards either side of E1 to find an exact match in column H of the
inventory sheet. If it finds one then it returns the row number where
the match occurs. This is then used in the INDEX part of the formula
to return the contents from column A on the same row as where the
match occurred.

The formula worked fine in my test set up (just on one sheet, though),
so perhaps you didn't copy all of it - did you get both brackets from
the end?

Pete

 

[Pete_UK]

Actually, another thing it might be is if you use a semicolon rather
than comma to separate parameters in functions. This will depend on
your regional settings. If so, just use ; instead of ,

Pete

 

[Seantastic]

Thanks Pete! So far you are 2 for 2 with the best solutions.

Before I got your answer I re-use the solution you gave me for another one.
I copy the serial num column into A and used "replace" in that column to
remove the "ABBC.", the "_RM" and the "_FT" which left me just the middle
numbers. I then used your VLOOK solution from my last problems and it worke
great!!

Thank you so much for your help!!!