Duplicates in drop-down menu

%3CLINGO-SUB%20id%3D%22lingo-sub-2277625%22%20slang%3D%22en-US%22%3EDuplicates%20in%20drop-down%20menu%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2277625%22%20slang%3D%22en-US%22%3E%3CDIV%20class%3D%22_2m1qj%22%3E%3CP%3EI'm%20doing%20an%20Excel%20assignment%20for%20my%20MS%20Office%20course%2C%20and%20one%20part%20has%20you%20create%20a%20drop-down%20menu%20for%20this%20Customer%20Quick%20Lookup%20Table%20(Screenshot%201)%20that%20will%20allow%20the%20user%20to%20select%20one%20of%20the%20last%20names%20(Screenshot%202)%20from%20another%20table%20in%20a%20different%20worksheet%20(Screenshot%203).%20The%20rest%20of%20the%20form%20are%20to%20have%20formulas%20that%20will%20update%20depending%20on%20which%20last%20name%20is%20selected%20in%20the%20drop-down%20menu.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%3ERight%20now%2C%20my%20table%20structure%20and%20formulas%20look%20like%20this%3A%3C%2FSPAN%3E%3C%2FP%3E%3C%2FDIV%3E%3CDIV%20class%3D%22_2m1qj%22%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSTRONG%3ELast%20Name%3A%20%3C%2FSTRONG%3EHas%20the%20drop-down%20menu%3C%2FP%3E%3C%2FDIV%3E%3CDIV%20class%3D%22_2m1qj%22%3E%3CP%3E%3CSTRONG%3EFirst%20Name%3A%20%3C%2FSTRONG%3E%3DVLOOKUP(C8%2CTable4%2C2%2CFALSE)%3C%2FP%3E%3C%2FDIV%3E%3CDIV%20class%3D%22_2m1qj%22%3E%3CP%3E%3CSTRONG%3EAge%20of%20Traveler%3A%20%3C%2FSTRONG%3E%3DVLOOKUP(C8%2CTable4%2C4%2CFALSE)%3C%2FP%3E%3C%2FDIV%3E%3CDIV%20class%3D%22_2m1qj%22%3E%3CP%3E%3CSTRONG%3EDeparture%20Airport%20Code%3A%20%3C%2FSTRONG%3E%3DVLOOKUP(C8%2CTable4%2C8%2CFALSE)%3C%2FP%3E%3C%2FDIV%3E%3CDIV%20class%3D%22_2m1qj%22%3E%3CP%3E%3CSTRONG%3EFavorite%20Country%3A%20%3C%2FSTRONG%3E%3DVLOOKUP(C8%2CTable4%2C6%2CFALSE)%3C%2FP%3E%3C%2FDIV%3E%3CDIV%20class%3D%22_2m1qj%22%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ENow%20the%20issue%2C%20as%20the%20assignment%20instructions%20will%20point%20out%2C%20has%20to%20do%20with%20the%20last%20two%20entries.%20They%20both%20have%20the%20same%20last%20name%2C%20but%20only%20the%20first%20one%20(Dave%20Valerie)%20is%20working.%20If%20I%20try%20to%20select%20the%202nd%20Valerie%20(Erica)%20in%20the%20drop-down%20menu%2C%20it%20still%20only%20shows%20Dave's%20info.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%3EWhat%20should%20I%20do%20to%20fix%20this%3F%20And%20before%20you%20ask%20-%20no%2C%20I%20can't%20just%20change%20their%20last%20names%20to%20something%20else.%3C%2FSPAN%3E%3C%2FP%3E%3C%2FDIV%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2277625%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3ETraining%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2277703%22%20slang%3D%22en-US%22%3ERe%3A%20Duplicates%20in%20drop-down%20menu%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2277703%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F999500%22%20target%3D%22_blank%22%3E%40Freckled-Lili%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EIf%20you%20identify%20person%20only%20with%20last%20name%20you%20can%20do%20nothing%20with%20that.%20VLOOKUP%20finds%20first%20appeared%20last%20name%20and%20returns%20info%20for%20it.%20In%20general%20you%20may%20return%20info%20for%20the%20last%20name%20in%20the%20list%2C%20but%20that%20changes%20practically%20nothing.%3C%2FP%3E%0A%3CP%3EAs%20variant%20you%20may%20define%20bot%20last%20and%20first%20name%20from%20drop%20down%20list%20and%20VLOOKUP%20on%20it%20combination%2C%20or%20use%20other%20more%20reliable%20ID.%3C%2FP%3E%3C%2FLINGO-BODY%3E
Regular Visitor

I'm doing an Excel assignment for my MS Office course, and one part has you create a drop-down menu for this Customer Quick Lookup Table (Screenshot 1) that will allow the user to select one of the last names (Screenshot 2) from another table in a different worksheet (Screenshot 3). The rest of the form are to have formulas that will update depending on which last name is selected in the drop-down menu.

 

Right now, my table structure and formulas look like this:

 

Last Name: Has the drop-down menu

First Name: =VLOOKUP(C8,Table4,2,FALSE)

Age of Traveler: =VLOOKUP(C8,Table4,4,FALSE)

Departure Airport Code: =VLOOKUP(C8,Table4,8,FALSE)

Favorite Country: =VLOOKUP(C8,Table4,6,FALSE)

 

Now the issue, as the assignment instructions will point out, has to do with the last two entries. They both have the same last name, but only the first one (Dave Valerie) is working. If I try to select the 2nd Valerie (Erica) in the drop-down menu, it still only shows Dave's info.

 

What should I do to fix this? And before you ask - no, I can't just change their last names to something else.

1 Reply

@Freckled-Lili 

If you identify person only with last name you can do nothing with that. VLOOKUP finds first appeared last name and returns info for it. In general you may return info for the last name in the list, but that changes practically nothing.

As variant you may define bot last and first name from drop down list and VLOOKUP on it combination, or use other more reliable ID.