How do I replace the content of a cell with a lookup?

realzeroburn used Ask the Experts™
Here's the situation.  I have 2 excel sheets.  On Sheet 1 is a column with a list of part numbers, for example:


On Sheet 2 are two columns, one with the part number, and one with the name.  For example:

PROD1XX        Apples
PROD2XX        Bananas
PROD3XX        Pears

Sheet 1 has maybe a couple hundred items, but Sheet 2 has all the products, so a couple thousand.

How can I replace the column in Sheet 1 that says "PROD1XX" with "Apples" by looking up the information from Sheet 2?  I'm not sure if you can do this from within just Excel- or if a script is required.  Any solution would work though, I could also do this from a CSV with a text editor if that'd make it easier.

Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Assuming that the data on sheet 1 is in column A and the data on sheet 2 is in columns A and B

Enter this formula in B2
and copy it down
Select column B
copy column B
Paste special to same location with values only
delete column A


You're awesome!  Worked Perfectly!!

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial