VLOOKUP: How do I match one column to another sheet and join them without losing data?

Question

I have two sheets that look something like this:

Sheet1

id | phone | age
0    123     23
1    456     42
2    789     36

Sheet2
id | city | country
0   madrid  spain
1   nyc     usa
2   dubai   uae 
3   london  england
4   lisbon  portugal

My goal is to have a sheet that looks like this:

Sheet3
id | phone | age | city | country
0    123     23   madrid  spain  
1    456     42   nyc     usa
2    789     36   dubai   uae
3                 london  england
4                 lisbon  portugal

I've been using this formula:

=ARRAYFORMULA({'Sheet1'!A$1:C$4, VLOOKUP('Sheet1'!A$1:A$4,{'Sheet2'!A$1:A$6, 'Sheet2'!B$1:C$6}, {2,3}, false)})

This is what I get:

Sheet3
id | phone | age | #N/A | #N/A
0    123     23   madrid spain
1    456     42   nyc    usa 
2    789     36   dubai  uae

So as you can see, it is leaving out the column headers from Sheet2 in the combined table and it leaves out any rows where the id doesn't match. How do I tell it to leave those rows in and leave the cells blank and include the column headers from Sheet2?

user_5632629 · Answer

try:

=ARRAYFORMULA(QUERY({A:C, IFNA(VLOOKUP(IF(A:A<>"", A:A, "×"), E:G, {2, 3}, 0)); 
 FILTER({E2:E, IFERROR(E2:F/0), F2:G}, NOT(COUNTIF(E2:E, A2:A)))}, 
 "where Col1 is not null order by Col1", 1))

VLOOKUP: How do I match one column to another sheet and join them without losing data?

1 Answers