Excel match cells

Question

I would like to match the two tables as follows:

Table 1

Project	Staff
1	A, B, CD, E
2	B, E, X

Table 2

Staff	Office
A	NY
B	CA
CD	ON
E	NY
X	FL

I would like to add a column to the Table 1 as below, after matching.

Table Results

Project	Staff	Office
1	A, B, CD, E	NY, CA, ON, NY
2	B, E, X	CA, NY, FL

Would you mind to teach me how to do it with Excel or any other free tool? Many thanks.

Some duplicate question included on the top. Note that if you have even got access to the beta-channel of ms365 insiders, you could even opt to use `TEXTSPLIT()` instead of `FILTERXML()`. — JvdV, Mar 21 '22 at 12:11
In the **Office** column of Table 1, use: `=BYROW( B2:B3, LAMBDA(a, TEXTJOIN(", ", 1, UNIQUE( LOOKUP(FILTERXML( "~~" & SUBSTITUTE( a, ", ", "" ) & "~~", "//s" ),tOffices[Staff],tOffices[Office]) ) ) ) )` where B2:B3 is Staff Column of the Table 1. I added UNIQUE to remove duplicates in the output, assuming that would be desirable. — mark fitzpatrick, Mar 21 '22 at 13:48

Mushroomator · Answer 1 · 2022-03-21T11:46:18.040

Using Google Sheets and Google Apps Script this would work. Given the following input.

Rows	1	2
1	Project	Staff
2	1	A, B, CD, E
3	2	B, E, X
4	Staff	Office
5	A	NY
6	B	CA
7	CD	ON
8	E	NY
9	X	FL

the following script

function matchTables(){
    const spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
    // get sheet by the name of your sheet
    const sheet = spreadsheet.getSheetByName("my-sheet");

    // get the cells containing your table
    // start in  2nd row, 1st col for 2 rows and columns
    const tab1Body = sheet.getRange(2, 1, 2, 2);
    const tab2Body = sheet.getRange(5, 1, 5, 2);
    // target cells for new column
    const newColumnRange = sheet.getRange(2, 3, 2, 1);

    const staffToOffice = tab2Body.getValues();
    // each staff must have one or zero offices otherwise some adjustments need to be done
    const mapStaffOffice = new Map();
    staffToOffice.forEach(row => {
        // map staff to office
        // e.g. A => NY
        mapStaffOffice.set(row[0], row[1]);
    })

    // get values of tab1
    const projects = tab1Body.getValues();
    const newColumn = [];
    projects.forEach(project => {
        const staffStr = project[1];
        const staffMembers = new String(staffStr).split(", ");

        const projectStaffOffices = [];
        staffMembers.forEach(member => {
            if(!mapStaffOffice.has(member)){
                Logger.log(`Ǹo office found for member of staff ${member}.`)
            }
            // get office for this particular staff member
            else projectStaffOffices.push(mapStaffOffice.get(member));
        })
        // done. all offices for this project have been found
        // first row of the new column is created, separate them with , and add to new column
        newColumn.push([projectStaffOffices.join(", ")]);
    });

    // done. all projects are processed. now write the new colum
    newColumnRange.setValues(newColumn);
    // set heading
    sheet.getRange(1, 3).setValue("Office");
  }

will produce this output: