MS Excel – join two tables

Here is a standard issue – you have several Excel tables that stores different aspects about the same thing. And you need to jump between those spreadsheets back and forward. A lot of fun, but not very effective 🙂 .

In SQL you can do joins. Excel is powerful enough to do the same and with less lines. MATCH and INDEX functions will help us. MATCH finds the place of occurrence and INDEX uses it to take the value.

Suppose I have two sheets: Sheet1 and Sheet2;

Column A in Sheet1 has similar values as column A in Sheet2;

My goal is to collect values located in column B in Sheet2 and map them with server names in Sheet1.

Place this command into C2 and drag it:

=INDEX(Sheet2!$B$2:$B$6,MATCH(Sheet1!A2,Sheet2!$A$2:$A$6,0))

MATCH(Sheet1!A2,Sheet2!$A$2:$A$6,0) – this command finds the place of Sheet1!A2 value in the list Sheet2!$A$2:$A$6

INDEX() – takes the link to a value from the column that I’m interested in Sheet2!$B$2:$B$6 based on the value returned by MATCH function and gives the index.

I’m using $ signs to fix the range.

Advertisements

Leave a Comment here

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s