Auto-Populate data in a table using a drop-down list selection from other sheet tables

[rdoulaghsingh]

Hello,

I have 4 sheets in a workbook (Sheet1, Sheet2, Sheet3 and Sheet4)
On Sheet1 I have a dropdown in B1 to select from a list of fruits. For eg. Apple, Banana, Cantaloupe,
Below that dropdown there is a "Store" table with headers Store(A5), Isle(B5), Shelf(C5)

On sheets 2,3 and 4, I have separate tables for each fruit. So sheet 2 has a table for apple, sheet3 has a table for banana and sheet4 has a table for cantaloupe. The name of the fruit is located in several different cells in the A column and there are blank spaces between the names of the recurring fruit in that same column.

I'm looking for vba code to use the dropdown value from Sheet1 as the lookup value to populate the table below for Store, Isle and Shelf from the table array of Sheet2, 3, and 4 (keeping in mind that there are some blank cells between fruit names in column B on those sheets) and return the adjacent columns where the search matches the fruit from the respective sheet in the table on sheet1. So populate the table on sheet1 with the Store, Isle and Shelf of the fruit.

I have included images of all sheets. Any help would be greatly appreciated! Thanks in advance!