How do I align two tables based on a unique column with Excel?

Question

I have two tables in excel, the first is a complete list of products with some basic info, the other has only selected products in and more information about them, but is lacking some of the information in the first table.

I want to merge or align them so that all the data for each product is in one table.

E.g.

Table 1

ID      Price      Weight
1       £2         100g
2       £3         250g
3       £3.5       70g
4       £2.75      25g
5       £0.8       50g
.
.
.

Table 2

ID     Colour     Sold     Stock ...
3      Red        98       102
4      Blue       50       50
.
.

.

I could use vlookup but that would only return one columns value, the second table has over 100 columns and I want to avoid writing that many! Any ideas appreciated.

Answer 1

I think there would be nothing bad in using VLOOKUP for this; you could write a formula based upon the actual COLUMN() for the vlookup-index in a way like this for example:

Assuming the first table data starts from A1 cell and data are contained in columns A:E and the second table is contained on a different worksheet named "Table2" on cells from A1 to ZZ100, put the following formula on the first row and on the first free column:

=IFERROR(VLOOKUP(A1,Table2!$A$1:$ZZ$100,COLUMN()-4,FALSE),"")

notice I used "COLUMN()-4" as index for the VLOOKUP because last data column on the table is E while, if it was D, I would have used "COLUMN()-3" and if it was F "COLUMN()-5" instead... I hope you understand the operation mechanism. It would be then very easy to simply paste the formula over the 100 (or even more) subsequent columns without the pain of having to change the vlookup-index in the formula for every column!

---

Otherwise you can check out even: DigDB or Merge Tables Wizard or Power-Query for this.

---

You could check also: this other superuser question or here if you want to learn about data consolidation and, finally, this one if you want to solve with VBA.