Combining different tables in excel by looking up values

Question

Okay, so let's say I have two tables in Excel. I want to create a third table that looks up data from the other two and combines them. I'll explain what I mean

Here is my first table:

It has a number, which is like a primary key, and a corresponding name for each entry.

I also have a second table:

This one just contains some random info, and there's numerous entries. The Client Name is not listed here, but it does have the key for the client that each entry corresponds to.

I want to create a third table that shows me all the Client Info data, but replaces the key with the actual client name. So basically, wherever it sees a key of "1", it'll look that up from the first table and find "Comet" instead.

The desired table would look like this:

What kind of formula would I need to pull data from one table based on a value? Note that all my tables are in different worksheets.

did any of the answers below resolved your question? if yes accept an answer to mark the post as Answered... If not, give more details on what you want to achieve. — Oscar Anthony
– Oscar Anthony, Commented Jan 27, 2016 at 14:43

teylyn · Accepted Answer · 2016-01-19 20:59:28Z

2

Assuming that the three tables are in Sheet1, Sheet2, Sheet3, this is the formula you need in Sheet3, cell A2

=vlookup(Sheet2!A2,Sheet1!$A2$b$6,2,false)

Copy down as many rows as there are rows in Sheet2

If you need the client info from Sheet2, use this in Sheet3, cell B2 and copy down

=sheet2!B2

answered Jan 19, 2016 at 20:59

teylyn

36.2k6 gold badges58 silver badges76 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

marc_s · Accepted Answer · 2016-01-20 16:50:58Z

1

As you put an "SQL" tag, I assume that you are also looking for an SQL based answer. So in addition to @teylyn suggestion, you can use the following SQL query if you are working with SQL databases:

SELECT table1.Client_Name, table2.Client_Info
FROM table1
RIGHT JOIN table2 ON table1.ID_Number = table2.ID_Number

Here is what this query does:

The RIGHT JOIN will return all rows from the right table (here table2), with the matching rows in the left table (here table1) according to the condition specified by the ON clause (here, if the ID Number is the same). Then, the SELECT clause will return a table with ONLY the columns specified after the keyword SELECT.

edited Jan 20, 2016 at 16:50

marc_s

760k186 gold badges1.4k silver badges1.5k bronze badges

answered Jan 19, 2016 at 21:14

Oscar Anthony

1905 silver badges18 bronze badges

Collectives™ on Stack Overflow

Combining different tables in excel by looking up values

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related