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

"Pete_UK" wrote:

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

On Nov 1, 2:28 am, Pete_UK wrote:
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

On Oct 31, 9:26 pm, Seantastic
wrote:



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" wrote:
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

On Oct 31, 4:58 pm, Seantastic
wrote:
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- Hide quoted text -

- Show quoted text -- Hide quoted text -

- Show quoted text -