SOLVED

Setting up data for a word merge

New Contributor

Hi All,

 

I've set up a sheet for which a clients information gets entered and this is then merged onto various forms in word. 

 

I need to add a company and company relevant details (1 of 4 to choose) on to the same row as the client information. What i want is to simply select the company from a list (data validation?) and then the remaining information is automatically input next to it.

 

So far I have set up a table on sheet 2 with the list of the 4 companies in A2-A5. There are then 3 more columns of data for each company that needs to be transferred over. 

 

The company selection on sheet 1 is column L with the remaining company info to automatically go into M, N, O.

 

When selecting the company from the drop down list, is there a way the remaining info automatically put into the cells next to it?

 

Thanks

 

3 Replies

Hi @Daibear 

 

this is a classical situation for a VLOOKUP.

DTE_0-1642751898231.png

Here is my example:

=VLOOKUP($L2;Sheet2!$A$2:$D$5;2;FALSE)

 

You just need to adjust the "2" in the formula, which gives to target column number, so it will be 3 for Info2 and 4 for Info3

 

 

@Martin_Weiss 

Another option for office 365

Benny_1857_0-1642759433703.png

 

best response confirmed by Daibear (New Contributor)
Solution

@Daibear 

I agree with @Martin_Weiss that this is a classic VLOOKUP problem.  Not that I have used VLOOKUP for many years.  I have used XLOOKUP ever since it appeared and before that INDEX/MATCH.   Now, a lookup by company as foreign key would be

= XLOOKUP(foreignKey, Table1[company], Table1)

There are vastly more complicated formulas available in 365 insider that will return an entire table of foreign key matches as a single array formula, but they are not relevant here.