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

Question

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.

Answer 1

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).