If the numeric part of the item number will not be repeated in other item
numbers, try this:

With your list in Columns A:B....

A1: Item
A2: T HO 0586 C
A3: T HO 0569 C
etc

B1: Name
B2: Rose
B3: Lilly
etc


D1: Item
D2: 586

E1: Name
E2:
=IF(ISBLANK(D2),"",INDEX($A$2:$B$13,SUMPRODUCT(--ISNUMBER(FIND(D2,$A$2:$A$13))*ROW(INDIRECT("1:"&RO WS($A$2:$A$13)))),2))
In that instance the formula would return: Rose

copy that formula down as far as needed

Does that help?

***********
Regards,
Ron


"NTaylor" wrote:

Okay... that is getting me closer... How can I use a wildcard to recognize
part of the number in the first sheet as a lookup in the second? Like:

Sheet1:
ITEM NAME
568
569

Sheet2:
ITEM NAME
T HO 0586 C Rose
T HO 0569 C Lilly
T HO 0570 C Tulip

Thanks!!! This is SO helpful!
--
Nicki Taylor


"Dave Peterson" wrote:

=vlookup() looks like it would work ok.

=if(a1="","",vlookup(a1,sheet2!a:b,2,false))

You may want to read Debra Dalgleish's notes:
http://www.contextures.com/xlFunctions02.html

NTaylor wrote:

Which formula can I use to get product names to transfer onto other sheets?
Like:

Sheet1:
ITEM# NAME
T0589 Rose
T0575 Lilly
T0575 Tulip

Sheet2:
ITEM# NAME
T0589
T0575

I would like for it to recognize the item# in sheet to and automatically
look for the product name from sheet 2 and fill it in. I tried a few "IF"
formulas, but w/out luck. Thanks for any help...
--
Nicki Taylor

--

Dave Peterson