Help searching through multiple tables to pull data into a specific table

Sorry if my title isn't exactly specific; I wasn't really sure how to succinctly state my problem.

Here's an example of what I'm working with:
Example3.xlsx

Basically, I have a list of items on the left where the user can input the vendor code for the preferred vendor to buy from. Based on that, I want the sheet to automatically populate the AS PURCHASED UNITS table by pulling the information from the correct vendor table. I have no clue how to go about this. Currently I'm setting it up as a series of IF statements (since there's only a handful of current vendors that I need to work with) but as more vendors are added IF statements will get incredibly messy and be a pain to adjust with each new vendor. The second sheet in the example is how the vendor part of the drop-down lists are set up (if that helps). The Preferred Vendor column draws from the vendor code list on the drop-down sheet.

I'm not averse to using macros/VB, as long as it would automatically update as more vendors are added (or would update with a button click).

Any help would be greatly appreciated.

HI Cyali,
I dnt seem to understand the problem fully.
Correct me where i am wrong.......
1. You have, at the moment 3 vendors.
2. The user selects the preferred vendor via drop down in the range b7:b11 in sheet 1.
3. Based on the above you want the range d7:k11 to automatically populate.

Now I want to know that...
1. There are 5 items under each vendor, so if a user selects vendor1 which item should the auto populated table show for the selected vendor.

Helo Cyali,

Please see the attached file. I have employed the same logic that our friend Rylo has used. Minor Changes though
1. No helper column used
2. A new table is made in the sheet Dropdown which is used the the formulae which will ensure that the dynamic requirements are met with



Example3.xlsx

Certainly!

Offset basically means moving away from a certain reference and returning a particular cell or range. The function has the following syntax :-OFFSET(reference,rows,cols,height,width)

reference: - the reference from where the function has to move. Say cell c7
rows:- is the number of rows the reference has to move away from the reference. Say 2 rows. i.e the reference will move 2 rows down. (it can be negative also to move the reference upwards).
cols:- similar to rows above, is the number of columns the reference has to move away. Say 3 columns (move 3 columns right). It can have negative also to move reference to left.
height:-is the number of rows the function should refer to moving away a certain number of rows and columns. Say 4 rows.
width:- is the number of columns the function should refer to moving away a certain number of rows and columns. Say 5 columns.

if we employ the above information in the Offset function syntax it would be
=OFFSET(reference,rows,cols,height,width)
=OFFSET(c7 ,2 ,3,4,5)
i.e. move away from c7 by 2 rows, 3 columns and refer 4 rows and 5 columns. it will return a range of 4 rows and 5 columns 2 rows down and 3 columns right to the base reference that was c7.

I hope that is clear.

Hi Delighted to know tht the explanation helped!