Returning value in secocolumn based on selected value in first col

[Kicker]

I am trying to set up a catalog look up in which the first collumn of the
worksheet1 is values selected from a named range in collumn 1 on worksheet3
(using data validation)

Worksheet 3 contains the following:
Collumn1 = list of part numbers - named range = Catalog_Number
Collumn2 = corresponding list of part names - named range = Part_Name

What I want to do is to have the 2nd collumn of worksheet1 automatically
completed with the corresponding part name based upon the value (part number)
selected in worksheet1-collumn1.

The idea is to allow a user to enter a part number in worksheet1:column1 and
the corresponding part name will be filled in automatically next to the entry
in collumn2

Can I do this without programming?

Thanks

 

[T. Valko]

Let's assume your drop down is on Sheet1 in cell A1.

To get the part name from sheet3:

=INDEX(Part_Name,MATCH(A1,Catalog_Number,0))