Jan 20 2022 12:44 PM
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
Jan 21 2022 12:00 AM
Hi @Daibear
this is a classical situation for a VLOOKUP.
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
Jan 21 2022 02:04 AM
Jan 21 2022 03:06 AM
SolutionI 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.
Jan 21 2022 03:06 AM
SolutionI 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.