As an internal join in Excel (e.g. using VLOOKUP)

Question

Is there a way to internally join two different Excel spreadsheets using VLOOKUP?

In SQL, I would do it like this:

SELECT id, name FROM Sheet1 INNER JOIN Sheet2 ON Sheet1.id = Sheet2.id; 

Sheet1:

 +----+------+ | ID | Name | +----+------+ | 1 | A | | 2 | B | | 3 | C | | 4 | D | +----+------+ 

Sheet2:

 +----+-----+ | ID | Age | +----+-----+ | 1 | 20 | | 2 | 21 | | 4 | 22 | +----+-----+ 

And the result will be:

 +----+------+ | ID | Name | +----+------+ | 1 | A | | 2 | B | | 4 | D | +----+------+ 

How can I do this in VLOOKUP? Or is there a better way to do this other than VLOOKUP?

Thanks.

Answer 1

First, let's get a list of the values ​​that exist in both tables. If you are using excel 2010 or later, in Sheet 3 A2, enter the following formula:

 =IFERROR(AGGREGATE(15,6,Sheet2!$A$1:$A$5000/(COUNTIF(Sheet1!$A$1:$A$5000,Sheet2!$A$1:$A$5000)>0),ROW(1:1)),"") 

If you are using 2007 or earlier, use this array formula:

 =IFERROR(SMALL(IF(COUNTIF(Sheet1!$A$1:$A$5000,Sheet2!$A$1:$A$5000),Sheet2!$A$1:$A$5000),ROW(1:1)),"") 

As an array formula, copy and paste into the formula bar, then press Ctrl-Shift-Enter instead of Enter or Tab to exit edit mode.

Then copy as many lines as you want. This will create an ID'd list that is in both lists. This assumes the identifier is a number, not text.

Then with this list we use vlookup:

 =IF(A2<>"",VLOOKUP(A2,Sheet1!A:B,2,FALSE),"") 

Then, the value from Sheet 1 that matches is returned.

Answer 2

You can get this result with Microsoft Query.

First select Data > From other sources > From Microsoft Query

Then select Excel Files *.

In the "Select Workbook" windows, you must select the current Workbook.

Then, in the query wizard window, select sheet1 $ and sheet2 $ and click the ">" button.

Click "Next" and the visual query editor will open.

Click the SQL button and paste this query:

 SELECT `Sheet1$`.ID, `Sheet1$`.Name, `Sheet2$`.Age FROM`Sheet1$`, `Sheet2$` WHERE `Sheet1$`.ID = `Sheet2$`.ID 

Finally close the editor and place the table where you need it.

The result should look like this: