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.
Best Answer
A quick way to do it is indeed to use
vlookup
. For example: in F1 I'd putwhich collects the data from two columns, eliminates repetition, and sorts.
Then in G1 put
which takes the matching values from B column. Similarly, for H1 use
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
withfilter(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 byvlookup
.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.