Combine VLOOKUP and Data Validation drop list

%3CLINGO-SUB%20id%3D%22lingo-sub-1411350%22%20slang%3D%22en-US%22%3ECombine%20VLOOKUP%20and%20Data%20Validation%20drop%20list%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1411350%22%20slang%3D%22en-US%22%3E%3CP%3EI%20am%20trying%20to%20use%20VLOOKUP%20which%20works%20but%20I%20want%20to%20know%20if%20there%20are%20multiple%20results%20in%20the%20table_array%20can%20it%20combine%20with%20data%20validation.%20I%20can%20get%20them%20both%20working%20but%20not%20together.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIn%20data%20validation%20source%20i%20have%20put%26nbsp%3B%3DIF(B11%3D%22Bristol%20121%22%2C'Data%20Sheet'!%24B%248%3A%24B%2415)%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EFor%20the%20Vlookup%20I%20have%20put%26nbsp%3B%3DVLOOKUP(B11%2CTable10%5B%23All%5D%2C2%2CFALSE)%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EBasically%20if%20Bristol%20121%20is%20selected%20then%20it%20will%20add%20a%20drop%20list%20for%20the%20contacts%20for%20that%20site.%20on%20the%20front%20page.%3C%2FP%3E%3CP%3EI%20have%20attached%20a%20test%20sheet%20for%20an%20example%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1411350%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1411900%22%20slang%3D%22en-US%22%3ERe%3A%20Combine%20VLOOKUP%20and%20Data%20Validation%20drop%20list%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1411900%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F635930%22%20target%3D%22_blank%22%3E%40Mark_Bry%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EMark%3C%2FP%3E%3CP%3ENot%20quite%20sure%20what%20you%20are%20trying%20to%20do%20here%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThe%20dropdown%20list%20for%20the%20Contact%20seems%20to%20work%20ok%20i.e.%20if%20Bristol%20121%20the%20site%20then%20the%20correct%20list%20of%20names%20appear.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAre%20you%20looking%20to%20populate%20other%20fields%20as%20well%20on%20the%20front%20page.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EBTW%20-%20what%20if%20Bristol%20121%20is%20not%20selected%20-%20what%20results%20would%20you%20like%20then%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20attach%20an%20updated%20sheet%20-%20but%20not%20at%20all%20sure%20this%20is%20what%20you%20are%20looking%20for.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EPlease%20clarify.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Ethanks%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EPeter%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1412039%22%20slang%3D%22en-US%22%3ERe%3A%20Combine%20VLOOKUP%20and%20Data%20Validation%20drop%20list%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1412039%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20Peter%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%20for%20coming%20back%20to%20me%20on%20this.%20Unfortunately%20I%20had%20to%20strip%20out%20most%20of%20the%20info%20from%20the%20sheet%20to%20share%20online%20so%20it's%20not%20a%20great%20example.%26nbsp%3B%3C%2FP%3E%3CP%3EBasically%20on%20the%20front%20sheet%20when%20you%20select%20the%20client%20it%20adds%20the%20contact%20and%20address%20etc.%20The%20trouble%20I%20have%20is%20one%20of%20our%20clients%20have%20about%205%20contacts.%20I%20have%20been%20using%20VLOOKUP%20which%20works%20fine%20for%20all%20the%20sites%20with%20only%201%20contact.%20Bristol121%20however%20will%20only%20show%20the%20top%20result%20in%20the%20lookup.%26nbsp%3B%3C%2FP%3E%3CP%3EI%20want%20to%20know%20if%20only%20when%20more%20than%20one%20result%20they%20can%20appear%20in%20a%20drop%20down%20list%20in%20the%20contacts%20cell%3F%26nbsp%3B%20I%20added%20a%20%3DIF(B11%3D%22Bristol%20121%22%2C'Data%20Sheet'!%24B%248%3A%24B%2415)%20in%20the%20data%20validation%20but%20it%20overwrites%20the%20VLOOKUP%20formula%20rather%20than%20work%20off%20the%20back%20of%20it.%20(I%20may%20be%20using%20the%20wrong%20formula%20but%20my%20knowledge%20is%20basic.%26nbsp%3B%3C%2FP%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F667968%22%20target%3D%22_blank%22%3E%40peteryac60%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1412472%22%20slang%3D%22en-US%22%3ERe%3A%20Combine%20VLOOKUP%20and%20Data%20Validation%20drop%20list%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1412472%22%20slang%3D%22en-US%22%3E%3CP%3EAssuming%20you%20can%20rely%20on%20the%20Table%20to%20be%20sorted%20by%20Client%2C%20you%20could%20set%20up%20a%20named%20formula%2C%20(Formulas%5CNames%20Manager%3CBR%20%2F%3E%3CBR%20%2F%3EName%3A%20ContactList%3CBR%20%2F%3EScope%3A%20Workbook%3CBR%20%2F%3ERefersTo%3A%20%3DINDEX(Table1%5BContact%5D%2CMATCH(B11%2CTable1%5BClient%5D%2C0))%3AINDEX(Table1%5BContact%5D%2CMATCH(B11%2CTable1%5BClient%5D%2C1))%3CBR%20%2F%3E%3CBR%20%2F%3EFor%20your%20Data%20Validation%2C%20select%20List%20and%20Source%3A%20%3DContactList%3CBR%20%2F%3E%3CBR%20%2F%3EData%20validation%20doesn't%20seem%20to%20like%20having%20structured%20table%20references%20in%20it's%20Source%2C%20so%20there's%20an%20added%20step%20of%20using%20a%20named%20formula.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1412512%22%20slang%3D%22en-US%22%3ERe%3A%20Combine%20VLOOKUP%20and%20Data%20Validation%20drop%20list%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1412512%22%20slang%3D%22en-US%22%3EAlso%2C%20before%20you%20key%20the%20name%20into%20the%20name%20manager%2C%20be%20sure%20to%20select%20the%20cell%20in%20which%20you%20want%20the%20dropdown%20to%20appear%20(the%20B11%20cell%20reference%20in%20the%20formula%20is%20relative%20to%20whatever%20cell%20is%20active%20when%20you%20key%20the%20formula%20-%20I'm%20assuming%20the%20active%20cell%20when%20it's%20entered%20is%20to%20the%20immediate%20right%20-%20C11).%20So%20if%20you%20want%20the%20data%20validation%20to%20apply%20to%20range%20C2%3AC20%2C%20then%20you%20would%20select%20C2%3AC20%2C%20and%20with%20C2%20as%20the%20active%20cell%20in%20the%20selection%2C%20the%20named%20formula%20becomes%20(have%20to%20change%20B11%20to%20B2).%3CBR%20%2F%3E%3CBR%20%2F%3E%3DINDEX(Table1%5BContact%5D%2CMATCH(B2%2CTable1%5BClient%5D%2C0))%3AINDEX(Table1%5BContact%5D%2CMATCH(B2%2CTable1%5BClient%5D%2C1))%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1413480%22%20slang%3D%22en-US%22%3ERe%3A%20Combine%20VLOOKUP%20and%20Data%20Validation%20drop%20list%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1413480%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F635930%22%20target%3D%22_blank%22%3E%40Mark_Bry%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHi%20Mark%2C%3C%2FP%3E%3CP%3Eis%20this%20a%20complete%20list%20of%20your%20clients%2Fcontacts%20or%20just%20a%20subset%3F%3C%2FP%3E%3CP%3EFor%20example%2C%20is%20it%20likely%20that%20you%20will%20add%20another%20client%20who%20might%20have%20(say)%203%20contacts%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
Occasional Contributor

I am trying to use VLOOKUP which works but I want to know if there are multiple results in the table_array can it combine with data validation. I can get them both working but not together.

 

In data validation source i have put =IF(B11="Bristol 121",'Data Sheet'!$B$8:$B$15)

 

For the Vlookup I have put =VLOOKUP(B11,Table10[#All],2,FALSE)

 

Basically if Bristol 121 is selected then it will add a drop list for the contacts for that site. on the front page.

I have attached a test sheet for an example

5 Replies
Highlighted

@Mark_Bry 

 

Mark

Not quite sure what you are trying to do here?

 

The dropdown list for the Contact seems to work ok i.e. if Bristol 121 the site then the correct list of names appear.

 

Are you looking to populate other fields as well on the front page.

 

BTW - what if Bristol 121 is not selected - what results would you like then?

 

I attach an updated sheet - but not at all sure this is what you are looking for.

 

Please clarify.

 

thanks,

 

Peter

 

 

 

 

Highlighted

Hi Peter 

 

Thanks for coming back to me on this. Unfortunately I had to strip out most of the info from the sheet to share online so it's not a great example. 

Basically on the front sheet when you select the client it adds the contact and address etc. The trouble I have is one of our clients have about 5 contacts. I have been using VLOOKUP which works fine for all the sites with only 1 contact. Bristol121 however will only show the top result in the lookup. 

I want to know if only when more than one result they can appear in a drop down list in the contacts cell?  I added a =IF(B11="Bristol 121",'Data Sheet'!$B$8:$B$15) in the data validation but it overwrites the VLOOKUP formula rather than work off the back of it. (I may be using the wrong formula but my knowledge is basic. 

@peteryac60 

Highlighted

Assuming you can rely on the Table to be sorted by Client, you could set up a named formula, (Formulas\Names Manager

Name: ContactList
Scope: Workbook
RefersTo: =INDEX(Table1[Contact],MATCH(B11,Table1[Client],0)):INDEX(Table1[Contact],MATCH(B11,Table1[Client],1))

For your Data Validation, select List and Source: =ContactList

Data validation doesn't seem to like having structured table references in it's Source, so there's an added step of using a named formula.

Highlighted
Also, before you key the name into the name manager, be sure to select the cell in which you want the dropdown to appear (the B11 cell reference in the formula is relative to whatever cell is active when you key the formula - I'm assuming the active cell when it's entered is to the immediate right - C11). So if you want the data validation to apply to range C2:C20, then you would select C2:C20, and with C2 as the active cell in the selection, the named formula becomes (have to change B11 to B2).

=INDEX(Table1[Contact],MATCH(B2,Table1[Client],0)):INDEX(Table1[Contact],MATCH(B2,Table1[Client],1))
Highlighted

@Mark_Bry 

Hi Mark,

is this a complete list of your clients/contacts or just a subset?

For example, is it likely that you will add another client who might have (say) 3 contacts?