1

I have two tables.

Table 1
ID FIRST NAME
A01 John
A02 Peter
B01 Marta
Table 2

Note: This table purposely has more entries.

ID LAST NAME
A02 Smith
B01 Clevens
A03 Rogers
A01 Brown

Both tables represent the same people with First Names and Last Names, identified by ID. How I can join the data into one table grouped by ID correctly?

Desired results

ID NAME SURNAME
A01 John Brown
A02 Peter Smith
A03 Rogers
B01 Marta Clevens

2 Answers 2

0

Single Formula Version G2

=SORT(MAP(UNIQUE(FLATTEN(A2:A,D2:D)), LAMBDA(_id, 
  { _id, 
    IFERROR(VLOOKUP(_id, A2:B,2,FALSE)),
    IFERROR(VLOOKUP(_id, D2:E,2,FALSE)) }
 )))
  1. Use FLATTEN to join the ID columns from the two tables into a single combined ID column
  2. Apply UNIQUE to the combined column to get the list of lookup values
  3. MAP the lookup values into a LAMBDA function and assign an arbitrarily named variable _id to represent the mapped lookup values in the formula
  4. Create a 1x3 array {col1, col2, col3 } for each mapped _id :
        Col1:  _id
        Col2:  FIRST NAME from VLOOKUP *
        Col3:  LAST NAME from VLOOKUP *
         * IFERROR returns a blank instead of an error from VLOOKUP if value not found
  5. Optionally SORT the resulting table by _id (in this formula) or other column as preferred.

1

I think my first approach is to create a new table with all the unique ID, and use the VLOOKUP function to bring in the [NAME, SURNAME] columns into that new table.

enter image description here

If you're not familiar with VLOOKUP, it's a great function. This screenshot, should explain what it's doing better.

Obviously, I would put the new table in it's own sheet, I just put it all in one sheet for simplicity and to show you the examples.

enter image description here

Here is a link to a Google Sheet with a VLOOKUP example.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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