I have a spreadsheet that contains customer information including, ( by column) equipment, name, address and zip code. I have another spreadsheet with employee information and zip codes they are responsible for. By column employee name and zip code. I would like to create a new table with customer information and the employee that is responsible for that site by matching the site zip code to the employee name
bear with me here.
If it's acceptable, then in a column on your customer information spreadsheet we're going to add an index/match formula.
If it's not acceptable...do it anyway (on a copy!) for giggles. It'll give you an idea for how to do it on a new spreadsheet.
Everyone tells people to use vlookup. It's clunky AF. I am not a fan.
Since you have a unique identifier this is a shoo in for an index/match function.
Name your new column "Employee Responsible" or something.
In the cell under your heading you're going to put =index(RANGE OF EMPLOYEE NAMES, match(RANGE THAT HAS THE ZIPCODES OF CLIENTS, RANGE OF ZIPCODES ON YOUR EMPLOYEE SPREADSHEET, 0)) Make those all absolute references and see what happens.
If you did it right the Employee responsible for the company in the row should appear. If not...we broke something. If it does error out, check your references to make sure they're all absolute ( should have dolla signs in em: $A$1)
Here's a picture if that helps. I left a formula (it's pink text) in the yellow table for you so you can see what it's doing
I would like to suggest a two steps solution, help you to create Master Table.
Enter this array (CSE) formula in Result Sheet's Cell
A29, finish with Ctrl+Shift+Enter and fill across.
Enter this formula in Master Sheet's cell
D29& fill it down.
Instead of executing entire exercise in New Sheet (
Result Sheet), you may also create
New Tablein any of the old Sheets either in
Adjust Sheet's name & cell references in the formula as needed.