show data from sheet 2 while selecting cell value from sheet 1

[azii]

hi there
i have two data sheets,
sheet 1 for summary
sheet 2 for data
i want that if i select cell b4 value and cell c4 value, it should get data from sheet 2 with matching my two selected cell values.

Sheet 1 (the Summary Sheet ( here under crop, Farmetr Type an Tubewell i need to select these three values and in sheet 2 i need to match and get a certain cell value

Crop	Farmer Type	Tubewell
Potato	Average	Electricity
			Title	Amount
			Total Expense	165,533.32
			Total Income	278,731.43
			Net Profit	113,198.11
			VCR	73.71

Sheet 2

Crop	Farmer Type	Amount Rs
Maize	Progressive	156,624
Maize	Progressive	278,731
Maize	Progressive	122,107
Maize	Progressive	0.78
Potato	Progressive	165,533
Potato	Progressive	278,731
Potato	Progressive	113,198
Potato	Progressive	0.68
Rice	Progressive	182,241
Rice	Progressive	278,731
Rice	Progressive	96,491
Rice	Progressive	0.53
Rice	Progressive	182,241
Rice	Progressive	278,731
Rice	Progressive	96,491
Rice	Progressive	0.53
Maize	Average	156,624
Maize	Average	278,731
Maize	Average	122,107
Maize	Average	0.78
Potato	Average	165,533
Potato	Average	278,731
Potato	Average	113,198
Potato	Average	0.68
Rice	Average	182,241
Rice	Average	278,731
Rice	Average	96,491
Rice	Average	0.53
Rice	Average	182,241
Rice	Average	278,731
Rice	Average	96,491
Rice	Average	0.53
Maize	PE/Tractor	156,624
Maize	PE/Tractor	278,731
Maize	PE/Tractor	122,107
Maize	PE/Tractor	0.78
Potato	PE/Tractor	165,533
Potato	PE/Tractor	278,731
Potato	PE/Tractor	113,198
Potato	PE/Tractor	0.68
Rice	PE/Tractor	182,241
Rice	PE/Tractor	278,731
Rice	PE/Tractor	96,491
Rice	PE/Tractor	0.53
Rice	PE/Tractor	182,241
Rice	PE/Tractor	278,731
Rice	PE/Tractor	96,491
Rice	PE/Tractor	0.53
Maize	Electricity	156,624
Maize	Electricity	278,731
Maize	Electricity	122,107
Maize	Electricity	0.78
Potato	Electricity	165,533
Potato	Electricity	278,731
Potato	Electricity	113,198
Potato	Electricity	0.68
Rice	Electricity	182,241
Rice	Electricity	278,731
Rice	Electricity	96,491
Rice	Electricity	0.53
Rice	Electricity	182,241
Rice	Electricity	278,731
Rice	Electricity	96,491
Rice	Electricity	0.53

Please help resolve the issue

 

[SanjayGMusafir]

i want that if i select cell b4 value and cell c4 value, it should get data from sheet 2 with matching my two selected cell values.

apparently looks no issue with your idea

We can propagate formula linking cells as dynamic references

Please share XL2BB of both sheets - It saves hazard to recreate the data and give you resolution promptly.

There to help you. Need not worry.

 

[azii]

mrexcel.xlsx
	F
49
Sheet2

 

[azii]

mrexcel.xlsx
	G
7
Sheet1

 

[SanjayGMusafir]

mrexcel.xlsx
	F
49
Sheet2

It's a blank XL2BB

Select the data you want to share and then copy as "Mini Sheet" to share data

Thanks a lot

 

[azii]

mrexcel.xlsx
	B	C	D	E	F
3	Crop	Farmer Type	Tubewell
4	Potato	Average	Electricity
5				Title	Amount
6				Total Expense	165,533.32
7				Total Income	278,731.43
8				Net Profit	113,198.11
9				VCR	73.71
Sheet1

Cell Formulas
Range	Formula
F6:F7	F6	=IF($B$4="Maize",'C:\Users\asif.zamir\Desktop\[VCR Test.xls]Sheet2'!I3,IF($B$4="Potato",'C:\Users\asif.zamir\Desktop\[VCR Test.xls]M-P-O-O-E-NP'!I74,IF($B$4="Rice",'C:\Users\asif.zamir\Desktop\[VCR Test.xls]M-P-O-O-PE-NP'!I72,IF($B$4="Citrus (Kinnow)",'C:\Users\asif.zamir\Desktop\[VCR Test.xls]M-P-O-O-PE-DAP'!I72:I72))))
F8	F8	=IF($B$4="Maize",'C:\Users\asif.zamir\Desktop\[VCR Test.xls]M-P-O-O-E-DAP'!I76,IF($B$4="Potato",'C:\Users\asif.zamir\Desktop\[VCR Test.xls]M-P-O-O-E-NP'!I76,IF($B$4="Rice",'C:\Users\asif.zamir\Desktop\[VCR Test.xls]M-P-O-O-PE-NP'!I74,IF($B$4="Citrus (Kinnow)",'C:\Users\asif.zamir\Desktop\[VCR Test.xls]M-P-O-O-PE-DAP'!I74:I74))))
F9	F9	=IF($B$4="Maize",'C:\Users\asif.zamir\Desktop\[VCR Test.xls]M-P-O-O-E-DAP'!I77,IF($B$4="Potato",'C:\Users\asif.zamir\Desktop\[VCR Test.xls]M-P-O-O-E-NP'!I77,IF($B$4="Rice",'C:\Users\asif.zamir\Desktop\[VCR Test.xls]M-P-O-O-PE-NP'!I75,IF($B$4="Citrus (Kinnow)",'C:\Users\asif.zamir\Desktop\[VCR Test.xls]M-P-O-O-PE-DAP'!I76))))

Cells with Data Validation
Cell	Allow	Criteria
B4	List	=$B$200:$B$203
C4	List	=$C$200:$C$201
D4	List	=$E$200:$E$201
F4	List	=$F$200:$F$201

 

[azii]

mrexcel.xlsx
	B	C	D
2	Crop	Farmer Type	Amount Rs
3	Maize	Progressive	156,624
4	Maize	Progressive	278,731
5	Maize	Progressive	122,107
6	Maize	Progressive	0.78
7	Potato	Progressive	165,533
8	Potato	Progressive	278,731
9	Potato	Progressive	113,198
10	Potato	Progressive	0.68
11	Rice	Progressive	182,241
12	Rice	Progressive	278,731
13	Rice	Progressive	96,491
14	Rice	Progressive	0.53
15	Rice	Progressive	182,241
16	Rice	Progressive	278,731
17	Rice	Progressive	96,491
18	Rice	Progressive	0.53
19	Maize	Average	156,624
20	Maize	Average	278,731
21	Maize	Average	122,107
22	Maize	Average	0.78
23	Potato	Average	165,533
24	Potato	Average	278,731
25	Potato	Average	113,198
26	Potato	Average	0.68
27	Rice	Average	182,241
28	Rice	Average	278,731
29	Rice	Average	96,491
30	Rice	Average	0.53
31	Rice	Average	182,241
32	Rice	Average	278,731
33	Rice	Average	96,491
34	Rice	Average	0.53
35	Maize	PE/Tractor	156,624
36	Maize	PE/Tractor	278,731
37	Maize	PE/Tractor	122,107
38	Maize	PE/Tractor	0.78
39	Potato	PE/Tractor	165,533
40	Potato	PE/Tractor	278,731
41	Potato	PE/Tractor	113,198
42	Potato	PE/Tractor	0.68
43	Rice	PE/Tractor	182,241
44	Rice	PE/Tractor	278,731
45	Rice	PE/Tractor	96,491
46	Rice	PE/Tractor	0.53
47	Rice	PE/Tractor	182,241
48	Rice	PE/Tractor	278,731
49	Rice	PE/Tractor	96,491
50	Rice	PE/Tractor	0.53
51	Maize	Electricity	156,624
52	Maize	Electricity	278,731
53	Maize	Electricity	122,107
54	Maize	Electricity	0.78
55	Potato	Electricity	165,533
56	Potato	Electricity	278,731
57	Potato	Electricity	113,198
58	Potato	Electricity	0.68
59	Rice	Electricity	182,241
60	Rice	Electricity	278,731
61	Rice	Electricity	96,491
62	Rice	Electricity	0.53
63	Rice	Electricity	182,241
64	Rice	Electricity	278,731
65	Rice	Electricity	96,491
66	Rice	Electricity	0.53
Sheet2

 

[SanjayGMusafir]

The sample data you have shared contains a lot of repetitive figures which don't help with propagating right results

Still I have tried to work with an initial solution. Check this and revert -

Try using in actual data and then ask for discrepancies.

Book1
	B	C	D	E	F	G	H	I	J	K
1								Crop	Farmer Type	Amount Rs
2	Crop	Farmer Type	Tubewell					Maize	Average	0.78
3	Potato	Average	Electricity					Maize	Average	1,22,107.14
4				Title	Amount	Solution		Maize	Average	1,56,624.29
5				Total Expense	FALSE	0.68		Maize	Average	2,78,731.43
6				Total Income	FALSE	0.68		Maize	Electricity	0.78
7				Net Profit	FALSE	0.68		Maize	Electricity	1,22,107.14
8				VCR	FALSE	0.68		Maize	Electricity	1,56,624.29
9								Maize	Electricity	2,78,731.43
10								Maize	PE/Tractor	0.78
11								Maize	PE/Tractor	1,22,107.14
12								Maize	PE/Tractor	1,56,624.29
13								Maize	PE/Tractor	2,78,731.43
14								Maize	Progressive	0.78
15								Maize	Progressive	1,22,107.14
16								Maize	Progressive	1,56,624.29
17								Maize	Progressive	2,78,731.43
18								Potato	Average	0.68
19								Potato	Average	1,13,198.11
20								Potato	Average	1,65,533.32
21								Potato	Average	2,78,731.43
22								Potato	Electricity	0.68
23								Potato	Electricity	1,13,198.11
24								Potato	Electricity	1,65,533.32
25								Potato	Electricity	2,78,731.43
26								Potato	PE/Tractor	0.68
27								Potato	PE/Tractor	1,13,198.11
28								Potato	PE/Tractor	1,65,533.32
29								Potato	PE/Tractor	2,78,731.43
30								Potato	Progressive	0.68
31								Potato	Progressive	1,13,198.11
32								Potato	Progressive	1,65,533.32
33								Potato	Progressive	2,78,731.43
34								Rice	Average	0.53
35								Rice	Average	0.53
36								Rice	Average	96,490.65
37								Rice	Average	96,490.65
38								Rice	Average	1,82,240.78
39								Rice	Average	1,82,240.78
40								Rice	Average	2,78,731.43
41								Rice	Average	2,78,731.43
42								Rice	Electricity	0.53
43								Rice	Electricity	0.53
44								Rice	Electricity	96,490.65
45								Rice	Electricity	96,490.65
46								Rice	Electricity	1,82,240.78
47								Rice	Electricity	1,82,240.78
48								Rice	Electricity	2,78,731.43
49								Rice	Electricity	2,78,731.43
50								Rice	PE/Tractor	0.53
51								Rice	PE/Tractor	0.53
52								Rice	PE/Tractor	96,490.65
53								Rice	PE/Tractor	96,490.65
54								Rice	PE/Tractor	1,82,240.78
55								Rice	PE/Tractor	1,82,240.78
56								Rice	PE/Tractor	2,78,731.43
57								Rice	PE/Tractor	2,78,731.43
58								Rice	Progressive	0.53
59								Rice	Progressive	0.53
60								Rice	Progressive	96,490.65
61								Rice	Progressive	96,490.65
62								Rice	Progressive	1,82,240.78
63								Rice	Progressive	1,82,240.78
64								Rice	Progressive	2,78,731.43
65								Rice	Progressive	2,78,731.43
Sheet1

Cell Formulas
Range	Formula
F5:F6	F5	=IF($C$5="Maize",'/Users/SGM/Library/Containers/com.microsoft.Excel/Data/Documents/C:\Users\asif.zamir\Desktop\[VCR Test.xls]Sheet2'!I3,IF($C$5="Potato",'/Users/SGM/Library/Containers/com.microsoft.Excel/Data/Documents/C:\Users\asif.zamir\Desktop\[VCR Test.xls]M-P-O-O-E-NP'!I74,IF($C$5="Rice",'/Users/SGM/Library/Containers/com.microsoft.Excel/Data/Documents/C:\Users\asif.zamir\Desktop\[VCR Test.xls]M-P-O-O-PE-NP'!I72,IF($C$5="Citrus (Kinnow)",'/Users/SGM/Library/Containers/com.microsoft.Excel/Data/Documents/C:\Users\asif.zamir\Desktop\[VCR Test.xls]M-P-O-O-PE-DAP'!I72:I72))))
G5,G7	G5	=XLOOKUP(1,($I$2:$I$65=$B$3)*($J$2:$J$65=$C$3),$K$2:$K$65)
G6	G6	=XLOOKUP(1,($I$2:$I$65=$B$3)*($J$2:$J$65=$D$3),$K$2:$K$65)
F7	F7	=IF($C$5="Maize",'/Users/SGM/Library/Containers/com.microsoft.Excel/Data/Documents/C:\Users\asif.zamir\Desktop\[VCR Test.xls]M-P-O-O-E-DAP'!I76,IF($C$5="Potato",'/Users/SGM/Library/Containers/com.microsoft.Excel/Data/Documents/C:\Users\asif.zamir\Desktop\[VCR Test.xls]M-P-O-O-E-NP'!I76,IF($C$5="Rice",'/Users/SGM/Library/Containers/com.microsoft.Excel/Data/Documents/C:\Users\asif.zamir\Desktop\[VCR Test.xls]M-P-O-O-PE-NP'!I74,IF($C$5="Citrus (Kinnow)",'/Users/SGM/Library/Containers/com.microsoft.Excel/Data/Documents/C:\Users\asif.zamir\Desktop\[VCR Test.xls]M-P-O-O-PE-DAP'!I74:I74))))
F8	F8	=IF($C$5="Maize",'/Users/SGM/Library/Containers/com.microsoft.Excel/Data/Documents/C:\Users\asif.zamir\Desktop\[VCR Test.xls]M-P-O-O-E-DAP'!I77,IF($C$5="Potato",'/Users/SGM/Library/Containers/com.microsoft.Excel/Data/Documents/C:\Users\asif.zamir\Desktop\[VCR Test.xls]M-P-O-O-E-NP'!I77,IF($C$5="Rice",'/Users/SGM/Library/Containers/com.microsoft.Excel/Data/Documents/C:\Users\asif.zamir\Desktop\[VCR Test.xls]M-P-O-O-PE-NP'!I75,IF($C$5="Citrus (Kinnow)",'/Users/SGM/Library/Containers/com.microsoft.Excel/Data/Documents/C:\Users\asif.zamir\Desktop\[VCR Test.xls]M-P-O-O-PE-DAP'!I76))))
G8	G8	=XLOOKUP(1,($I$2:$I$65=$B$3)*($J$2:$J$65="PE/Tractor"),$K$2:$K$65)

 

[azii]

mrexcel.xlsx
	B	C	D
2	Crop	Farmer Type	Amount Rs
3	Maize	Progressive	156,624
4	Maize	Progressive	278,731
5	Maize	Progressive	122,107
6	Maize	Progressive	0.78
7	Potato	Progressive	165,533
8	Potato	Progressive	278,731
9	Potato	Progressive	113,198
10	Potato	Progressive	0.68
11	Rice	Progressive	182,241
12	Rice	Progressive	278,731
13	Rice	Progressive	96,491
14	Rice	Progressive	0.53
15	Maize	Average	156,624
16	Maize	Average	278,731
17	Maize	Average	122,107
18	Maize	Average	0.78
19	Potato	Average	165,533
20	Potato	Average	278,731
21	Potato	Average	113,198
22	Potato	Average	0.68
23	Rice	Average	182,241
24	Rice	Average	278,731
25	Rice	Average	96,491
26	Rice	Average	0.53
27	Maize	PE/Tractor	156,624
28	Maize	PE/Tractor	278,731
29	Maize	PE/Tractor	122,107
30	Maize	PE/Tractor	0.78
31	Potato	PE/Tractor	165,533
32	Potato	PE/Tractor	278,731
33	Potato	PE/Tractor	113,198
34	Potato	PE/Tractor	0.68
35	Rice	PE/Tractor	182,241
36	Rice	PE/Tractor	278,731
37	Rice	PE/Tractor	96,491
38	Rice	PE/Tractor	0.53
39	Maize	Electricity	156,624
40	Maize	Electricity	278,731
41	Maize	Electricity	122,107
42	Maize	Electricity	0.78
43	Potato	Electricity	165,533
44	Potato	Electricity	278,731
45	Potato	Electricity	113,198
46	Potato	Electricity	0.68
47	Rice	Electricity	182,241
48	Rice	Electricity	278,731
49	Rice	Electricity	96,491
50	Rice	Electricity	0.53
Sheet2

 

[azii]

mrexcel.xlsx
	B	C	D	E
2	Crop	Farmer Type	Amount Rs
3	Maize	Progressive	156,624	total amount
4	Maize	Progressive	278,731	Total Income
5	Maize	Progressive	122,107	Net Profit
6	Maize	Progressive	0.78	VCR
7	Potato	Progressive	165,533
8	Potato	Progressive	278,731
9	Potato	Progressive	113,198
10	Potato	Progressive	0.68
11	Rice	Progressive	182,241
12	Rice	Progressive	278,731
13	Rice	Progressive	96,491
14	Rice	Progressive	0.53
15	Maize	Average	156,624
16	Maize	Average	278,731
17	Maize	Average	122,107
18	Maize	Average	0.78
19	Potato	Average	165,533
20	Potato	Average	278,731
21	Potato	Average	113,198
22	Potato	Average	0.68
23	Rice	Average	182,241
24	Rice	Average	278,731
25	Rice	Average	96,491
26	Rice	Average	0.53
27	Maize	PE/Tractor	156,624
28	Maize	PE/Tractor	278,731
29	Maize	PE/Tractor	122,107
30	Maize	PE/Tractor	0.78
31	Potato	PE/Tractor	165,533
32	Potato	PE/Tractor	278,731
33	Potato	PE/Tractor	113,198
34	Potato	PE/Tractor	0.68
35	Rice	PE/Tractor	182,241
36	Rice	PE/Tractor	278,731
37	Rice	PE/Tractor	96,491
38	Rice	PE/Tractor	0.53
39	Maize	Electricity	156,624
40	Maize	Electricity	278,731
41	Maize	Electricity	122,107
42	Maize	Electricity	0.78
43	Potato	Electricity	165,533
44	Potato	Electricity	278,731
45	Potato	Electricity	113,198
46	Potato	Electricity	0.68
47	Rice	Electricity	182,241
48	Rice	Electricity	278,731
49	Rice	Electricity	96,491
50	Rice	Electricity	0.53
Sheet2