Forum Discussion

Mark_Bry's avatar
Mark_Bry
Copper Contributor
May 22, 2020

Combine VLOOKUP and Data Validation drop list

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

excel
Formulas and Functions

5 Replies

  • JMB17's avatar
    JMB17
    Bronze Contributor
    May 22, 2020
    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))
  • JMB17's avatar
    JMB17
    Bronze Contributor
    May 22, 2020

    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.

  • peteryac60's avatar
    peteryac60
    Iron Contributor
    May 22, 2020

    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

     

     

     

     

    • Mark_Bry's avatar
      Mark_Bry
      Copper Contributor
      May 22, 2020

      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 

      • peteryac60's avatar
        peteryac60
        Iron Contributor
        May 23, 2020

        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?

         

Resources