# Union of two lists of key-value pairs

In a spreadsheet I have two lists (key-value pairs basically) that look like this:

``````[A]  [B]     [D] [E]
a   23       b  hello
b   42       d  world
c   11       f  good
f   17       g  bye
h   88
``````

And I would like to get something that looks like this:

``````a  23    -
b  42    hello
c  11    -
d  -     world
f  17    good
g  -     bye
h  88    -
``````

Is there a way to achieve this inside Google Sheets? I could download the csv file and write a program for it, but looking around on this site it seems that Google Sheets is powerful and a lot of these things can be done without leaving it.

A quick way to do it is indeed to use `vlookup`. For example: in F1 I'd put

``````=sort(unique({A1:A; D1:D}))
``````

which collects the data from two columns, eliminates repetition, and sorts.

Then in G1 put

``````=arrayformula(iferror(vlookup(F1:F, A\$1:B, 2, False)))
``````

which takes the matching values from B column. Similarly, for H1 use

``````=arrayformula(iferror(vlookup(F1:F, D\$1:E, 2, False)))
``````

If your spreadsheet has a lot of empty rows at the bottom, these array formulas are slightly inefficient in that they look up a lot of empty values. In this case, replacing `F1:F` with `filter(F1:F, len(F1:F))` helps: this filters down to nonempty values in F.

Technical remarks: `iferror(...)` suppresses error messages that arise when nothing is found by `vlookup`. `arrayformula` is used to avoid having to drag the formula down the column: it processes the entire column at once, and returns an array of results.

• Beautiful! This works. (One catch in my spreadsheet, which I just realized, is that the "key" columns A and D actually have a text with a hyperlink, as I pasted them from somewhere. So the F column gets "duplicates", where the text is the same but the hyperlinks are different. Somehow the G and H columns still show the right results each of the two times (i.e. vlookup takes only the text and ignores the hyperlink I guess). So I need to either strip the hyperlink and keep just the text for each of the rows of A and D, or change the formula for F to somehow consider only the text. Any ideas?) Feb 5 '16 at 1:29
• To remove hyperlinks manually is easy: select a range, right-click and choose "Unlink". I'll look into the formula approach.
– user79865
Feb 5 '16 at 1:38
• Joining with an empty string works: `=sort(unique({""&A1:A; ""&D1:D}))`
– user79865
Feb 5 '16 at 1:44
• Thank you! Somehow even after selecting "unlink" for columns A and D (which indeed seemed to work), and clearing out column F and re-entering the expression, still F had links in it (and therefore duplicates). But the joining with an empty string worked perfectly. Thanks again. Feb 5 '16 at 1:48