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   88And I would like to get something that looks like this:a  23    -b  42    helloc  11    -d  -     worldf  17    goodg  -     byeh  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.
Union of two lists of key-value pairs
google spreadsheets
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.