CODEWITHSUNDEEP
excel
user2951063
user2951063

Reputation: 131

How can I join data from 2 different excel sheets based on one common row?

I have an excel spreadsheet with 2 separate sheets within. The first sheet has customer details (Customer_ID | Surname | First Name | Address | Phone Number | etc).

The second sheet contains sale item data (item_id | Description | Price | Customer_id)

Currently in the second sheet, the customer_id column is blank. What I would like to be able to do is enter the customer_id in a row alongside item details, and then have the adjacent cells in that same row populated with the customer data from the first sheet. This combined data would then be used to create a receipt.

Each customer has a unique customer_id, though some of them have the same names (there are multiple John Smiths)

I have played around with VLOOKUP but haven't been able to achieve the desired result. I'm not very good at this. Still learning.

I understand how this would done in SQL, by joining the two sheets (or tables) on the customer_id column, but am not sure how this translates to excel.

Many thanks for your help.

Upvotes: 8

Views: 29543

Answers (2)

sancho.s ReinstateMonicaCellio
sancho.s ReinstateMonicaCellio

Reputation: 15561

Say your first (second) sheet is named 'A' ('B'), Customer_ID column in 'A' ('B') is A (D), headers are in row 1 in both cases, and the source table spans the range A!A2:D4. Then in cell E2 of sheet 'B', you would enter the formula

VLOOKUP($D2,A!$A$2:$D$4,COLUMN()-COLUMN($D2)+1)

This formula can be copy-paste'd into new rows/columns of sheet 'B'. You would only have to replace the range of your source table (A!$A$2:$D$4) by a suitable value prior to copy-paste.

If you think you would be changing the range of your source table you can:

1- Use a source range with enough room for expansion (depending on the contents of your sheets, it may not be appropriate).

2- Use a named range (possibly preferable).

Upvotes: 6

Mike Sheehan
Mike Sheehan

Reputation: 530

You're on the right track. Your second sheet should look like this:

item_id | Description | Price | Customer_id | Surname | ...

1 | description | 5.00 | 1 | =VLOOKUP($A$4, Sheet1!$A$2:$B$5, 2)

VLOOKUP details: A4 is the customer id on this sheet Sheet1!A2:B5 is the cell range of all customers in your first sheet 2 is the column in your first sheet that you want to display in the cell

The dollar signs freezes the range when you copy and paste the formula into other cells

Upvotes: 4

Related Questions