Using a lookup function
Sub GetPrice() Dim PartNum As Variant Dim Price As Double PartNum = InputBox("Enter the Part Number") Sheets("Prices").activate Price = WorksheetFunction.VLookup(PartNum, range("PriceList"), 2, False) MsgBox PartNum & " costs " & Price End Sub