Return multiple values from another workbook, based on Column Headers in other workbook

Hello,

Hopefully I can explain this throroughly.

I have 4 different regions as column headers in cells A1,B1,C1,D1. These are in Workbook 1

I have another workbook, workbook 2, that has Regions in Column A, Countries in Column B and Customer Codes in column C.

I want to have a formula that uses A1(South America) in workbook 1, looks it up in column A:A in workbook 2 and returns ALL the matching customer codes in C:C that are in South America in A2 to A(however many country codes there are)

I then want to drag this formula to the right so that it will return all the customer codes for each Column Header.

Thanks in Advance.

Mike

Thank you, but this is more of what I am referring to. The data will be updated as new customers are added, I am trying to have the chart on sheet 2 be dynamic and update with any updates of information. (I will be using those lists of customers by region to create drop down lists elsewhere)

Example.xls

Thank you for the help. I'm taking what you've done and trying to work wtih it. I'm trying to avoid having to constantly drag formulas, and manually enter a number in column A when customers are added

A little additional information. The sheet with the regions etc. is a reference sheet. Whenever a new customer is added, someone will go in and fill in the information for the new customer across all colulmns(much more than just the region, country, name, but just keeping it simple here). Adding a formula column to the left, may make people think they're supposed to enter that information when adding a new dealer. I'm trying to make this idiot proof, and sustainable with as little modification as necessary.

Most my other formulas pulling from this reference are dynamic arrays for entire columns. They pull from the reference sheet and I never have to worry about expanding any formulas down to include any new dealers that are added. The information you provided, someone would need to go in, expand the formula down every time additional dealers are added. It's over 100 currently and growing.

Any suggestions to avoid this? In the meantime I will be using your idea hopefully as inspiration.

Formula:

Please Login or Register  to view this content.

This was more what I was looking for. I understand I need to set large parameters for this, but it avoids having to create extra columns. Thank you so much for your assistance. I will keep you're method in mind for other projects.