0

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

  • 1
    Standard VLOOKUP use. Read the help for that function. – Aganju Dec 13 '15 at 15:12
  • Welcome to Super User. We are not a script writing service. We expect users to tell us what they have tried so far (including any scripts they are using) and where they're stuck so that we can help with specific problems. Questions that only ask for scripts are too broad and are likely to be put on hold or closed. Please read How do I ask a good question?. – DavidPostill Dec 13 '15 at 15:27
  • Please Edit your post & add Sample data from both sheets,, and I think that the ZIP code is enough to get common data from both Sheets!! – Rajesh S Aug 22 '19 at 5:06
0

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

Photo of spreadsheet sample

  • Better Edit your post and rewrite the Formula in place of =index(RANGE OF EMPLOYEE NAMES, ,,, also add Sheet name with cell reference to specify formula reads it from where!! – Rajesh S Aug 22 '19 at 4:54
  • And I don't think that's what OP is looking for Table has to read common data matches Zip code & Name to create Results!! – Rajesh S Aug 22 '19 at 5:04
0

I would like to suggest a two steps solution, help you to create Master Table.

enter image description here

Step 1:

  • Enter this array (CSE) formula in Result Sheet's Cell A29, finish with Ctrl+Shift+Enter and fill across.

    {=IFERROR(INDEX(Customer!$A$19:$C$25,SMALL(IF(COUNTIF(Employee!$G$19:$G$21,Customer!$C$19:$C$25),MATCH(ROW(Customer!$A$19:$C$25),ROW(Customer!$A$19:$C$25)),""),ROWS(A29:$A$29)),COLUMNS($B$2:B2)),"")}

Step 2:

  • Enter this formula in Master Sheet's cell D29 & fill it down.

    =IFERROR(INDEX(Employee!$F$19:$F$21,MATCH($C$29:$C$34,Employee!$G$19:$G$21,0)),"")

N.B.

  • Instead of executing entire exercise in New Sheet (Result Sheet), you may also create New Table in any of the old Sheets either in Customer or Employee.

  • Adjust Sheet's name & cell references in the formula as needed.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.