Auto populate packing list from separate worksheet

There is only one person per location, anyway, so even if it were the right formula, which it isn't, it would only pull one person's details through. VLOOKUP will only ever return the first match it finds.

In order to simplify this (and anything else you want to do moving forward), I suggest you add a column to each of the location tables and give each client a unique customer code or number. When you have done that, post again and we'll show you how to do what you want.

A few things are clouding the issue here...
1. The sheets you refer to in the question, dont match the sheets in the sample file?
2. you mention you want by location, but your sample file seems to pull customer name?
3. You only give 1 sample answer, IF Sales Invoice is your required output? If that is all you want, then OK, if not, maybe show some extra sample anwers?

Remember, you know exactly what you want, we have no clue at all

Sorry, I don't think I was quite clear. So, every customer must have a unique ID - this then makes matching across different tables much easier, as names are not always unique. So, let's say that you have given Cameron Cox the unique client ID of 1, this then means that this number will stay with him everywhere he appears: he will need 1 next to his record in either of the two location tables. ALL clients need a unique ID. Do you think you can do that before we proceed?

The column for the ID number may be populated using:

Formula:

=IFERROR(INDEX(Employees!G$2:G$13,AGGREGATE(15,6,(ROW($2:$13)-1)/(Employees!$A$2:$A$13=$H$7),ROW(1:1))),"")

The rest of the columns (after undoing the merge and center for Pins) are populated using:

Formula:

=IF($A18="","",INDEX(Employees!B$2:B$13,MATCH($A18,Employees!$G$2:$G$13,0)))

Let us know if you have any questions.

It may be that an employee who makes an order gets a T-shirt, if that is the case then the formula for ID could be modified to read:

Formula:

=IFERROR(INDEX(Employees!G$2:G$13,AGGREGATE(15,6,(ROW($2:$13)-1)/((Employees!$A$2:$A$13=$H$7)*(Employees!C$2:C$13<>"")),ROW(1:1))),"")

If that isn't the case then an additional column, say column H, could be added to the Employees sheet that populates using: =OR(C2<>"",D2<>"",E2<>"",F2<>"")
The ID formula on the Packing List sheet could populate using:

Formula:

=IFERROR(INDEX(Employees!G$2:G$13,AGGREGATE(15,6,(ROW($2:$13)-1)/((Employees!$A$2:$A$13=$H$7)*(Employees!H$2:H$13=TRUE)),ROW(1:1))),"")

Let us know if you have any questions.

You're Welcome and thank you for the feedback.
As to understanding the formula, the first two arguments to the AGGREGATE function are SMALL (15) and Ignore error values (6). The * represents an intersection of two arrays, so what ever is true in both will result in a value of one and the rest will result in a value of 0. From there a good way to follow what the formula is doing is by using the Evaluate Formula feature found on the Formulas tab, however let us know if you have any questions.
Also please take a moment to mark the thread as 'Solved' using the thread tools link above your first post.
I hope that you have a blessed day.