Populate a column in a sheet based on data from 2 other sheets

[Goddess]

Hi,

Basically I have 2 sheets of data which I will enter the information manually (they act as reference). On the 3rd sheet, I need column K to auto polulate the 'Factor' based on the information on Sheet 1 & 2. Appreciate if anyone can help!

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M
1	Sheet 1					Sheet 2			Sheet 3
2
3	Group	Name	Division	Country		Country	Factor		Name	Sub total	Factor	Total
4	1	Apple	ABC	UK		UK	1		Apple	10	1	10
5	1	Orange	ABC	UK		Germany	1.5		Orange	15	1	15
6	1	Grape	ABC	UK		Turkey	2		Grape	20	1	20
7	2	Mango	XYZ	UK		Croatia	2.5		Mango	25	1	25
8	2	Banana	XYZ	UK					Banana	30	1	30
9	3	Cherry	DEF	Germany					Cherry	35	1.5	52.5
10	3	Durian	GHI	Germany					Durian	40	1.5	60
11	4	Peach	LMN	Turkey					Peach	45	2	90
12	5	Lychee	LMN	Croatia					Lychee	50	2.5	125
13	5	Pomelo	LMN	Croatia					Pomelo	55	2.5	137.5
14	0	NA							NA
15	0	NA							NA
16	0	NA							NA
17	0	NA							NA
18
Sheet2

Cell Formulas
Range	Formula
L4:L13	L4	=J4*K4

Thanks!

 

[Toadstool]

Hi Goddess,

So Group and Division have no bearing and a peach can only ever be in Turkey? Then this should do what you ask:

Goddess.xlsx
	A	B	C	D
1	Sheet 1
2
3	Group	Name	Division	Country
4	1	Apple	ABC	UK
5	1	Orange	ABC	UK
6	1	Grape	ABC	UK
7	2	Mango	XYZ	UK
8	2	Banana	XYZ	UK
9	3	Cherry	DEF	Germany
10	3	Durian	GHI	Germany
11	4	Peach	LMN	Turkey
12	5	Lychee	LMN	Croatia
13	5	Pomelo	LMN	Croatia
14	0	NA
15	0	NA
16	0	NA
17	0	NA
Sheet1

Goddess.xlsx
	A	B
1	Sheet 2
2
3	Country	Factor
4	UK	1
5	Germany	1.5
6	Turkey	2
7	Croatia	2.5
8
Sheet2

Goddess.xlsx
	A	B	C	D
1	Sheet 3
2
3	Name	Sub total	Factor	Total
4	Apple	10	1	10
5	Orange	15	1	15
6	Grape	20	1	20
7	Mango	25	1	25
8	Banana	30	1	30
9	Cherry	35	1.5	52.5
10	Durian	40	1.5	60
11	Peach	45	2	90
12	Lychee	50	2.5	125
13	Pomelo	55	2.5	137.5
14	NA
15	NA
16	NA
17	NA
Sheet3

Cell Formulas
Range	Formula
C4:C13	C4	=INDEX(Sheet2!$B$4:$B$999,MATCH(INDEX(Sheet1!$D$4:$D$9999,MATCH(A4,Sheet1!$B$4:$B$9999,0)),Sheet2!$A$4:$A$999,0))
D4:D13	D4	=B4*C4

 

[Goddess]

Hi Goddess,

So Group and Division have no bearing and a peach can only ever be in Turkey? Then this should do what you ask:

Goddess.xlsx
	A	B	C	D
1	Sheet 1
2
3	Group	Name	Division	Country
4	1	Apple	ABC	UK
5	1	Orange	ABC	UK
6	1	Grape	ABC	UK
7	2	Mango	XYZ	UK
8	2	Banana	XYZ	UK
9	3	Cherry	DEF	Germany
10	3	Durian	GHI	Germany
11	4	Peach	LMN	Turkey
12	5	Lychee	LMN	Croatia
13	5	Pomelo	LMN	Croatia
14	0	NA
15	0	NA
16	0	NA
17	0	NA
Sheet1

Goddess.xlsx
	A	B
1	Sheet 2
2
3	Country	Factor
4	UK	1
5	Germany	1.5
6	Turkey	2
7	Croatia	2.5
8
Sheet2

Goddess.xlsx
	A	B	C	D
1	Sheet 3
2
3	Name	Sub total	Factor	Total
4	Apple	10	1	10
5	Orange	15	1	15
6	Grape	20	1	20
7	Mango	25	1	25
8	Banana	30	1	30
9	Cherry	35	1.5	52.5
10	Durian	40	1.5	60
11	Peach	45	2	90
12	Lychee	50	2.5	125
13	Pomelo	55	2.5	137.5
14	NA
15	NA
16	NA
17	NA
Sheet3

Cell Formulas
Range	Formula
C4:C13	C4	=INDEX(Sheet2!$B$4:$B$999,MATCH(INDEX(Sheet1!$D$4:$D$9999,MATCH(A4,Sheet1!$B$4:$B$9999,0)),Sheet2!$A$4:$A$999,0))
D4:D13	D4	=B4*C4

Thanks!!!

 

[Toadstool]

Thanks!!!

You're welcome.