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 da...

Test sheet 1.xlsx31 KB

excel

Formulas and Functions

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