oops... Getting product name from other sheet

N

NTaylor

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...
 
R

Ron Coderre

Try this:

On Sheet2...
A2: (an item number)
B2: =VLOOKUP(A2,Sheet1!A1:B100,2,0)

Adjust range references to suit your situation.

Does that help?

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

NTaylor

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

NTaylor

I think it is close... but not quite. Here is what I was trying but the
wildcard wouldn't work:

=VLOOKUP(A11="*A11*", Products!C:C, Products!B:B,TRUE)

for the "*A11*" I was hoping it could find anything that has that number in
it plus more- but I think it is looking for that text sequence instead of in
that box. You know what I mean?
 
R

Ron Coderre

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:"&ROWS($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 said:
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!
Click to expand...
 
Top