data validation and cell population

[PinkHinky]

In column E, I have validated those cells using data validation allowing only items from a list to be populated. There are 7 physician names on this list. In column F I have an area for their physician number to go. Each physician has a three digit physician number exclusive to them. What I'm trying to do is when I select a physician from Column E's drop down box, I would like to have in the corresponding row in Column F, their unique physician number populating the cell. How would I go about doing this? I'm an intermediate level excel user.

 

[VoG]

Try using VLOOKUP http://www.contextures.com/xlFunctions02.html

 

[PinkHinky]

Vlookup doesn't work for this. Is there another method? I've tried using the "if" formula and I need to know how to combine multiple "ifs" to make this work.

 

[lenze]

If VLOOKUP doesn't work, you're not telling us the whole story. You could, of course use a Change Event (VBA Code).

Private Sub WorkSheet_Change(ByVal Target as Range)
If Target.Count <> 1 Then Exit Sub
If Target Column <> 5 Then Exit Sub
Select Case UCase(Target)
     Case "PHSY1" : Cells(Target.Row,6) = "123"
     Case "PHSY2" : Cells(Target.Row,6) = "456"
     'etc
     Case Else: 
End Select
End Sub

lenze