Excel Merge on matching IDs

Question

I've been Having a problem with excel regarding matching and merging cells. I have one table that contains some but not all IDs with some data (table1) and I have another that contains all IDs with different data(table2).

I want to match and merge these two tables so that table1 also contains the data that table 2 has, matched by ID.

I do not have enough rep to post picture so I'll try and show what I am struggling with in a code block.

Table 1

F1    Gnr
x12
x25
x37
x63

Table 2

F1    Gnr
x1    y8
x2    y3
x3    y29
etc.  etc.

Desired outcome:

F1     Gnr
x12    y56
x25    y38
x37    y98
x63    y42

This is a simplified version of the problem and I hope it helps to illustrate what I want done. I have tried VLOOKUP and Index and Match but for some reason it WILL NOT work.

edit: Below you find what piece of code I have been using:

=IFERROR(VLOOKUP(Sheet1!A$2;Sheet2!$A$1:$D$50;COLUMN(A1);FALSE);Sheet1!A2)

Here I believe I am looking up the value from sheet1A2 and trying to match that value to the complete table in sheet2. The result I get though is the value of Sheet1!A2 (the iferror value).?

Thanks.

Answer 1

You can use Powerquery (add-in from microsoft for Excel 2013, inbuilt for 2016)

1. Set up both tables as Excel tables (include headers)

2. Select cell in one of the tables > Data tab or powerquery (Excel 2013) > from table>

3. Window that opens > home> close and load to > only create connection>

4. Repeat for other table

5. You then have two workbook queries (one for each table)

6. Data ( or Powerquery) tab > New query > Combine queries > Merge

7. Select your table 1 (the one to update) then below your table 2, highlight both id columns and then left outer join type

8. Expand the new table insert to show the merged columns by clicking on the <>

9. Highlight the two columns in between that aren’t needed and right-click remove columns

10. Rename the last column as required

11. Then close and load to new sheet

12. Observe result

Answer 2

Your IFERROR does exactly what you might expect: it receives an error, and returns the "backup" value (in this case, Sheet1!A$2).

The underlying reason for this: your VLOOKUP returns an error!

The reason for this might be because none of the values in Table 1 are present in Table 2. (Or that might just be your example.) If this is true, you might want to consider using fuzzy matching (the last argument in VLOOKUP - either TRUE or FALSE).

It's also possible that there is a mistake in your code. Here's the formula I used for B2:

=VLOOKUP(A2;Sheet2!$A$1:$B$5;2;0)

In general, it's better not to use the IFERROR wrapper until you're sure that the inner code is working.