I am taking an advanced Google sheet course and I got the following assignment.

I have a first table in Google Sheet (let's call it T1) with two variables, company name and salesman. For each company name there may be one or several salesmen. If there is more than one salesman for each company, then the table has multiple rows. For instance, in the table below, company C1 has two salesmen, while others have one only:

Company	Salesman
C1	S1
C1	S2
C2	S1
C3	S1

This is a mapping table.

I have a second table (let's call it T2) which has only the company names (C1, C2, C3, etc.) and other metadata referred to that company (O1, O2, O3, etc.). So even if there are multiple salesmen for a company, in table T2, there is only one entry for each company and its metadata (i.e., one row), like in the below:

Company	Other
C1	O1
C2	O2
C3	O3

I want to create a third table, T3, which expands T2 to accomodate the multiple salesmen. In other words, if in T2 there is C1 which has two salesmen, i.e. S1 and S2, I want the T2 to add a second row for C1, so that both salesmen can be correctly captured:

Company	Other	Salesman
C1	O1	S1
C1	O1	S2
C2	O2	S1
C3	O3	S1

How can I do this? Any Google Sheet or VBA answer would be much appreciated. I believe in SQL this would be achieved via some join/pivoting, but I am not sure in Google Sheet.

CodePudding user response：

Excel ms365:

Formula in G1:

=LET(x,A1:A5,HSTACK(x,VLOOKUP(x,D1:E4,2,0),B1:B5))

Or, in terms of tables:

=LET(x,T_1[[#All],[Company]],HSTACK(x,VLOOKUP(x,T_2[#All],2,0),T_1[[#All],[Salesman]]))

---

Google-Sheets:

Formula in G1:

=INDEX({VLOOKUP(A1:A5,D1:E4,{1,2},0),B1:B5})

CodePudding user response：

use:

={INDEX(IFNA(VLOOKUP(A2:A, D2:E, {1, 2}, ))), B2:B}