VLOOKUP-How to Select One of Many Names

kstvns

New Member
Joined
Feb 27, 2011
Messages
20
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?
 

Some videos you may like

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
Joined
May 9, 2009
Messages
16,623
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
Joined
Sep 28, 2010
Messages
902
Office Version
  1. 365
Platform
  1. Windows
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
Joined
Feb 27, 2011
Messages
20
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? :confused:
 

kstvns

New Member
Joined
Feb 27, 2011
Messages
20
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?
 

Watch MrExcel Video

Forum statistics

Threads
1,108,816
Messages
5,525,061
Members
409,617
Latest member
Lenaf

This Week's Hot Topics

Top