choose value from dropdown and populate non-adjacent cells in same row

[docfrenzy]

I have read dozens of threads but I don't find this exact scenario described. I have a worksheet that is populating a series of dates with artists performing, with the venue, times, opening acts, etc. On a separate worksheet I have a list of venues, with cities and states, websites and ticketing URLs.

The main worksheet [events] contains the following header/columns (a1:m1):

Date​

Artist​

Venue​

Time (event)​

Time (doors)​

Opener(s)​

City​

ST​

Venue URL​

Ticket URL​

Cost (low)​

Cost (high)​

presale/code​

The lookup sheet [venues] contains the following header/columns (in cells a1:d1):

venue​

city​

ST​

web​

I want to be able to start typing a venue name in [events] column C (Venue), and as I start typing "p" I will see "Palace, The" -- and all other venues starting with "p" -- from which to choose. But ALSO, once I choose a value for [events].(Venue) -- let's say "Palace, The" -- I want everything else in the row for "Palace, The" -- [venue] columns (B, C, D) to populate the NON-adjacent cells in [events] columns (G, H, I).

Also, I anticipate adding more venues over time, so I'm hoping the solution is dynamic, in case the row reference changes. I expect to keep the main venues reference table in alpha order, but if it doesn't have to be kept that way -- or can't -- I can handle that.

I'm using an Excel 2013 equivalent at home, but if a better solution can be done with a later version of Excel, I have 2016 at work.

Thanks in advance for any help!

 

[SanjayGMusafir]

for Venue you need Data Validation for rest of the information to be populated INDEX-MATCH is the solution.

With Version 2013 or 2016 things shall be more or less same.

Depends on how you have stored your data VLOOKUP can also be used to populate Venue data.