text replacing

macdonaldf

New Member
Joined
Mar 29, 2002
Messages
1
Went through the first 15 pages or so until my eyes started to hurt, couldn't find an answer, although it probably is in here somewhere. Anyway, here is my situation.

I have 2 columns, 1st column list initials of person, 2nd column lists persons full name. I want to be able to enter into another cell the initials, and have it give me the person's name in that cell.

i.e. I punch in 'jd' ... when I hit enter, it replaces 'jd' with 'John Doe'

Thanks for any help you all can provide.
 

Excel Facts

VLOOKUP to Left?
Use =VLOOKUP(A2,CHOOSE({1,2},$Z$1:$Z$99,$Y$1:$Y$99),2,False) to lookup Y values to left of Z values.
try Lookup or Vlookup

With Lookup info must be in ascending order.

Vlookup is used most often. See Help.

With a table named rL

ABC ABC info
DEF DEF info

assuming code in A2

=vlookup(a2,rL,2,0)or =vlookup(a2,rL,2,False)

the 2 means look in second column
the o or False means you want an exact match
 
Upvote 0
Just to add to Dave's answer, If 2 or more people have the same initials then Vlookup will only return the name for the first set of initials it finds.
 
Upvote 0
On 2002-03-30 11:44, macdonaldf wrote:
Went through the first 15 pages or so until my eyes started to hurt, couldn't find an answer, although it probably is in here somewhere. Anyway, here is my situation.

I have 2 columns, 1st column list initials of person, 2nd column lists persons full name. I want to be able to enter into another cell the initials, and have it give me the person's name in that cell.

i.e. I punch in 'jd' ... when I hit enter, it replaces 'jd' with 'John Doe'

Thanks for any help you all can provide.

If I understand the question correctly, macdonaldf wants to input initials in a cell and get the entire name in the same cell.
Can it be done by vlookup?
Eli
 
Upvote 0
If I understand the question correctly, macdonaldf wants to input initials in a cell and get the entire name in the same cell.
Can it be done by vlookup?
Eli

No, you'd need either an extra column if you wanted to use VLookup or a possibly very long Change macro, if you wanted it in the same cell.
 
Upvote 0
Sorry if Vlookup is not appropriate.
I read the part about 2 columns but I guess I did not read the part about the "same" cell.

With the millions of cells available, I wonder whey 2 cells cannot be used.
The code column could always be hidden later.

You could also look at defined names.
This message was edited by Dave Patton on 2002-03-30 12:52
 
Upvote 0
You could insert this code in the sheets own module (rightclick on the sheettab, choose view code)
Whenever you type/change D2 it will make a vlookup in A1:B100 and return the value from column B. This is not perfect and personally i would do like the others suggested.

Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo finished
If Target.Address = "$D$2" Then
Target.Value = WorksheetFunction.VLookup(Target.Value, Range("A1:b100"), 2, 0)
End If
finished:
End Sub

regards Tommy
 
Upvote 0

Forum statistics

Threads
1,213,531
Messages
6,114,172
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top