# VLOOKUP-How to Select One of Many Names

#### kstvns

##### New Member
Using Excel 2003 with Win XP.

I am creating a timesheet. On a separate worksheet, I have three columns named LastName, FirstName, Section. On another worksheet, I inserted a formula in B1 that, based on the entry of the last ame, the first name and section will appear in cells B2 and B3, respectively. The problem is that more than one person has the same last name (e.g., Smith.)

If John Smith enters his last name, what can I do to make it not show Will Smith?

### Excel Facts

Using Function Arguments with nested formulas
If writing INDEX in Func. Arguments, type MATCH(. Use the mouse to click inside MATCH in the formula bar. Dialog switches to MATCH.

#### T. Valko

##### Well-known Member
Using Excel 2003 with Win XP.

I am creating a timesheet. On a separate worksheet, I have three columns named LastName, FirstName, Section. On another worksheet, I inserted a formula in B1 that, based on the entry of the last ame, the first name and section will appear in cells B2 and B3, respectively. The problem is that more than one person has the same last name (e.g., Smith.)

If John Smith enters his last name, what can I do to make it not show Will Smith?
You need to come up with some method of making the names unique.

On your master sheet enter the names something like this:

Smith(J), John
Smith(W), Will

Then, you'd look for Smith(J) or Smith(W).

If they both also happen to have the same first name then you can give them unique numbers like:

Smith(1), John
Smith(2), John

#### Haseeb Avarakkan

##### Well-known Member
Enter in C1

=COUNTIF(LastName,B1)

A2, control+shift+enter, not just enter, copy down...

=IF(ROWS(B\$2:B2)<=\$C\$1,SMALL(IF(LastName=\$B\$1,ROW(LastName)-MIN(ROW(LastName))+1),ROWS(B\$2:B2)),"")

B2, copy down...

=IF(N(\$A2),INDEX(LastName,\$A2),"")

Change LastName to the actual reference & for the Section.

#### kstvns

##### New Member
Haseeb,

Column A already has data in it. A1 is Last Name; A2 is First Name; A3 is Section; and A4 is Month. As a result, B1:B3 is where the information for A1:A3 will be placed/entered.

What modification to the formula do I need to make?

#### kstvns

##### New Member
I provided wrong information in my first post.

Using Excel 2003 with Win XP.

I am creating a timesheet. On a separate worksheet, I have three columns named LastName, FirstName, Section. On the timesheet, A1 is labeled Last Name; A2 is labeled First Name; A3 is labeled Section. I inserted a formula in B1 that, based on the entry of the last name, the first name and section will appear in cells B2 and B3, respectively. The problem is that more than one person has the same last name (e.g., Smith.)

If John Smith enters his last name, what can I do to make it not show Will Smith?

Replies
3
Views
42
Replies
18
Views
165
Replies
3
Views
31
Replies
1
Views
310
Replies
0
Views
41