14

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

In SQL, I would do it this way:

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 would 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 besides VLOOKUP?

Thanks.

Wabbage
  • 437
  • 3
  • 6
  • 18
  • Did you try? `=VLOOKUP(Sheet2!A1,Sheet1!A:B,2,False)` Where column A:A in both sheets hold the id and Column B on Sheet 1 has the name. It would go in B1 in Sheet 2 and copied down. – Scott Craner Feb 02 '16 at 22:13
  • Is there a way for it to return the columns with the IDs as well? – Wabbage Feb 02 '16 at 22:17
  • So you want to populate a new sheet with only the ids found in both sheets and the corresponding name from sheet 1? Will there ever be a time that there are ids on sheet 2 that are not on sheet 1? – Scott Craner Feb 02 '16 at 22:24
  • 1
    VLOOKUP won't duplicate rows. For example if two rows of 'A' in table1 matches three rows 'A' in table2, an inner join will result in six rows and VLOOKUP will result in two rows. If there are no matches VLOOKUP results in a row and inner join results in none – Nick.Mc Feb 02 '16 at 22:52
  • @ScottCraner Yes, and yes. In the latter's case, it would look like [this](http://www.tutorialspoint.com/sql/sql-inner-joins.htm). – Wabbage Feb 04 '16 at 14:59
  • @Nick.McDermaid Is there another way to do Inner Join in Excel's formula bar? – Wabbage Feb 04 '16 at 15:02
  • Could you mock up some data? – Scott Craner Feb 04 '16 at 15:22
  • @ScottCraner Done. I edited my post. – Wabbage Feb 04 '16 at 15:37
  • You can use Microsoft Queries to make inner joins – Seb Feb 04 '16 at 15:49
  • how would the data be displayed if the id is in sheet2 but not in sheet1? Would it even be in the output? – Scott Craner Feb 04 '16 at 17:08
  • @Seb I can't use MS Queries because I'm doing this through Java's JDBC. – Wabbage Feb 04 '16 at 17:49
  • @PuggyLongLegs doesn't matter because wou can make queries along worksheets themself – Seb Feb 04 '16 at 17:51
  • @ScottCraner The same thing will happen vice versa of my example. The result should only display rows that contain IDs that can be found in **both** sheets, since I'm inner-joining where the Sheet1's ID = Sheet2's ID. – Wabbage Feb 04 '16 at 17:52
  • @Seb I can't because I'm going to be working with tables that are not in the same server. – Wabbage Feb 04 '16 at 17:53
  • @PuggyLongLegs I know, if you are able to store your values in a worksheet, microsoft Query will work. The query will be executed on the sheets, not on the data object. let me post an answer. – Seb Feb 04 '16 at 17:56

3 Answers3

6

You can acheive this result using Microsoft Query.

First, select Data > From other sources > From Microsoft Query

enter image description here

Then select "Excel Files*".

In the "Select Workbook" windows, you have to select the current Workbook.

Next, in the query Wizard windows, select sheet1$ and sheet2$ and click the ">" button. enter image description here

Click Next and the query visual editor will open.

Click on 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 put the table where you need it.

The result should look like this : enter image description here

Seb
  • 1,230
  • 11
  • 19
  • Thanks, but my problem is that I have to do all these through Java (using JDBC & Apache POI). The only thing Excel will do by itself is display the results of calculations and logic from my Java code. – Wabbage Feb 04 '16 at 18:14
  • You can put this in your template Workbook and then call "update all" before displaying the result of the calculations – Seb Feb 04 '16 at 18:19
  • But my tables, columns, and conditions are dynamic. Unless I can create a template Workbook manually on Java every time I run my program. – Wabbage Feb 04 '16 at 18:29
4

First lets get a list of values that exist in both tables. If you are using excel 2010 or later then in Sheet 3 A2 put 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 then 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)),"")

Being an array formula, copy and paste into the formula bar then hit Ctrl-Shift-Enter instead of Enter or Tab to leave the edit mode.

Then copy down as many rows as desired. This will create a list of ID'd that are in both lists. This does assume that ID is a number and not text.

Then with that list we use vlookup:

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

This will then return the value from Sheet 1 that matches.

enter image description here

Scott Craner
  • 148,073
  • 10
  • 49
  • 81
0

For Basic Excel Join without formuales or Excel Macros. Please check the website http://exceljoins.blogspot.com/2013/10/excel-inner-join.html

Joins can Left Outer, Right Outer and Full Outer which used in rare ocassions, But we can achieve this for Excel Sheets, For more information check the below http://exceljoins.blogspot.com/

App Review
  • 1
  • For Basic Excel Join without formuales or Excel Macros. Please check the website http://exceljoins.blogspot.com/2013/10/excel-inner-join.html Joins can Left Outer, Right Outer and Full Outer which used in rare ocassions, But we can achieve this for Excel Sheets, For more information check the below http://exceljoins.blogspot.com/ – App Review Mar 02 '20 at 12:37
  • Please post the relevant sections of the answer here itself as links might change over time. – Harsh Wardhan Mar 02 '20 at 12:58