2

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.

danicotra
  • 2,036
Ben
  • 21

2 Answers2

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.

Community
  • 1
danicotra
  • 2,036
0

There is a direct way to do this with VLOOKUP (and XLOOKUP which wasn't available then), although doing it with Power Query or VBA would be the gold standard. Especially with such a large move.

Many functions can use what is called an "array constant" in Excel for one of their parameters. ("One"... just one... Excel takes only the first entry from any second/third/etc. array constants in a single function's parameters.)

And array constant takes the form of: {"horse","cow",2,3567,21,"pig",8}

You can use one in either of the first or third parameters in VLOOKUP with the output parameter (the third one) being the most commonly used.

To do a VLOOKUP for a value in K1, finding it in a lookup range of A1:F500, and wanting columns B, E, and F returned for it, you would use:

=VLOOKUP(K1, A1:F500, {2,5,6}, FALSE)

INDEX is probably where you see it used the most. When it is, it can often be because one wants to re-order the columns and/or repeat columns. (Think a tight set of columns in which any bit of data occurs once in a row, so perhaps a first name. But your users need to scroll right and left a lot causing it to leave the screen and for them to not have an easy time keeping the call "friendly" by using the customer's first name. So you'd like that column to appear every 10 columns or so. Lots of good, but whacky reasons.)

If you wanted that VLOOKUP but needed columns in a different order and column 2 to appear twice, your array constant might look like this: {6,2,5,2} and VLOOKUP would happily return exactly that.

So you can do this with familiar tools although Power Query is surely best of all.

Jeorje
  • 1