Here's a more complex but versatile answer. I created two dynamic defined names, Restaurants and Venue.
Restaurant is fairly simple
=Sheet2!$C$2:INDEX(Sheet2!$C$2:$C$100, COUNTA(Sheet2!$C:$C)-1)
Then to pull the proper venue addresses, I first position the active cell in B2 of sheet1 (next to the first restaurant you would choose (A2))
The formula for Venue is then
Modify your end cell (Z100) to properly represent the maximum number of restaurant names and venues you will have (i.e. M500 if you expect a max of 13 restaurants with up to 500 addresses)
See attachment.
Bookmarks