Auto fill group data using info from another worksheet based on multiple cells? - Formula Needed.

[Jessey277]

[h=2][/h]

Hi all,

Looking for some help with a formula to auto fill data from one worksheet to another by using a formula:

On our first worksheet we have codes and numbers.

The data is in multiple rows and in that row is a combination of codes such as A+E+T or A+E+F depending on product.

We want another 4 columns of associated data to be put in the next columns on sheet 1.

I understand the data has to be referred to somewhere so on worksheet 2 we have all the codes listed down columns (eg Column A, Column B, and Column C) and then next to it we have the next 4 columns with data relating to those combinations of codes.

So how can we ask excel to copy the relevant data from the reference worksheet (worksheet 2) into our data (worksheet 1)

I was hoping at this stage for a formula based solution.


For example, This is worksheet 1:
[TABLE="width: 837"]
<colgroup><col width="93" span="9" style="width:70pt"> </colgroup><tbody>[TR]
[TD="class: xl69, width: 93"]A [/TD]
[TD="class: xl68, width: 93"]B[/TD]
[TD="class: xl67, width: 93"]C[/TD]
[TD="class: xl66, width: 93"]D[/TD]
[TD="class: xl66, width: 93"]E[/TD]
[TD="class: xl66, width: 93"]F[/TD]
[TD="class: xl66, width: 93"]G[/TD]
[TD="class: xl66, width: 93"]H[/TD]
[TD="class: xl65, width: 93"]I[/TD]
[/TR]
[TR]
[TD="class: xl70"]A [/TD]
[TD="class: xl71"]E [/TD]
[TD="class: xl72, width: 93"]F [/TD]
[TD="class: xl73, width: 93"]0[/TD]
[TD="class: xl74, width: 93, align: right"]0[/TD]
[TD="class: xl74, width: 93, align: right"]0[/TD]
[TD="class: xl75, width: 93"]$0[/TD]
[TD="class: xl76, align: right"]$0.00 [/TD]
[TD="class: xl77, align: right"]$0.00 [/TD]
[/TR]
[TR]
[TD="class: xl78"]A [/TD]
[TD="class: xl79"]E [/TD]
[TD="class: xl80, width: 93"]T [/TD]
[TD="class: xl81, width: 93"]0[/TD]
[TD="class: xl82, width: 93, align: right"]0[/TD]
[TD="class: xl82, width: 93, align: right"]0[/TD]
[TD="class: xl83, width: 93"]$0[/TD]
[TD="class: xl84, align: right"]$0.00 [/TD]
[TD="class: xl85, align: right"]$0.00 [/TD]
[/TR]
[TR]
[TD="class: xl86"] A[/TD]
[TD="class: xl87"]S[/TD]
[TD="class: xl88, width: 93"]F [/TD]
[TD="class: xl89, width: 93"]0[/TD]
[TD="class: xl90, width: 93, align: right"]0[/TD]
[TD="class: xl90, width: 93, align: right"]0[/TD]
[TD="class: xl91, width: 93"]$0[/TD]
[TD="class: xl92, align: right"]$0.00 [/TD]
[TD="class: xl93, align: right"]$0.00 [/TD]
[/TR]
[TR]
[TD="class: xl94"]A[/TD]
[TD="class: xl95"]S[/TD]
[TD="class: xl96, width: 93"]T [/TD]
[TD="class: xl97, width: 93"]0[/TD]
[TD="class: xl98, width: 93, align: right"]0[/TD]
[TD="class: xl98, width: 93, align: right"]0[/TD]
[TD="class: xl99, width: 93"]$0[/TD]
[TD="class: xl100, align: right"]$0.00 [/TD]
[TD="class: xl101, align: right"]$0.00 [/TD]
[/TR]
</tbody>[/TABLE]
And this is worksheet 2

[TABLE="width: 467"]
<colgroup><col span="6"><col></colgroup><tbody>[TR]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[TD]G[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD]E[/TD]
[TD]F[/TD]
[TD="align: right"]1000[/TD]
[TD="align: right"]200[/TD]
[TD="align: right"]100[/TD]
[TD="align: right"]$1,000.00[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD]E[/TD]
[TD]T[/TD]
[TD="align: right"]5000[/TD]
[TD="align: right"]800[/TD]
[TD="align: right"]300[/TD]
[TD="align: right"]$1,500.00[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD]S[/TD]
[TD]F[/TD]
[TD="align: right"]3000[/TD]
[TD="align: right"]300[/TD]
[TD="align: right"]100[/TD]
[TD="align: right"]$2,000.00[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD]S[/TD]
[TD]T[/TD]
[TD="align: right"]3000[/TD]
[TD="align: right"]300[/TD]
[TD="align: right"]100[/TD]
[TD="align: right"]$2,500.00[/TD]
[/TR]
[TR]
[TD]N[/TD]
[TD]E[/TD]
[TD]F[/TD]
[TD="align: right"]650[/TD]
[TD="align: right"]40[/TD]
[TD="align: right"]20[/TD]
[TD="align: right"]$3,000.00[/TD]
[/TR]
[TR]
[TD]N[/TD]
[TD]E[/TD]
[TD]T[/TD]
[TD="align: right"]2200[/TD]
[TD="align: right"]220[/TD]
[TD="align: right"]20[/TD]
[TD="align: right"]$3,500.00[/TD]
[/TR]
[TR]
[TD]N[/TD]
[TD]S[/TD]
[TD]F[/TD]
[TD="align: right"]900[/TD]
[TD="align: right"]60[/TD]
[TD="align: right"]20[/TD]
[TD="align: right"]$4,500.00[/TD]
[/TR]
[TR]
[TD]N[/TD]
[TD]S[/TD]
[TD]T[/TD]
[TD="align: right"]600[/TD]
[TD="align: right"]50[/TD]
[TD="align: right"]20[/TD]
[TD="align: right"]$5,000.00[/TD]
[/TR]
</tbody>[/TABLE]



On Worksheet 1, we want it to insert the cells next to the corresponding code in worksheet 2 into the next 4 columns (so CELL D2 on worksheet 1 would be auto filled with the data from CELL D2 as the code is "1000", CELL E2 in worksheet 1 would have the same date autofilled as the code is also "E2" etc..).

I thought about doing a concat and then a vlookup but thinking there should be a formula?

First post so I hope I've posted this correctly with enough information. Any help will be much appreciated.

Kind Regards
Jessica​

 

[istiasztalos]

Hi Jessica,

If all the data you'll copy are values, then SUMIFS could do, you can add 3 sets of criteria that columns A, B, C have to match.

If you can have text as well, then I think you'll have to do the VLOOKUP or INDEX/MATCH on the concatenated A, B, C columns (not necessarily with the CONCATENATE formula, linking with & sign would also work)