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?




3 Replies

Hi @Daibear 


this is a classical situation for a VLOOKUP.


Here is my example:



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




Another option for office 365



best response confirmed by Daibear (New Contributor)


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.