Get values from other sheet base on two criteria

[inese_green]

Hello, everyone! I’m a new to the group!
I would like to solve my problem. I have two workbooks Data and ISO. I need to collect values from each cell in Data sheet from sheet ISO, based on Lab.Nr. and Sieve size (what matches these criteria).
I have created Named ranges: data_iso (Sheet ISO AC4:AP12) these are the values I need to get in Data sheet based on columns Lab.nr. and Row -Sieve size form ISO sheet
Lab.Nr - (Sheet ISO B:B)
Sieve_size_data - sieve size from Data sheet (C7:P7)
Sieve_size_ISO (AC3:AP3)

My formula is =INDEX(Data_iso,MATCH($B9,Lab.Nr,0),MATCH(Sieve_size_data,Sieves_size_ISO,0))

but the problem is, it brings back only one column of data...I have tride different way, but somethings wrong every time.

Hope to get help! Thank you.

Here is Sheet DATA

example2.xlsx
	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S
1				lab.Nr.
2				Sieve size
3
4
5	Lab. Nr.	Residue, % by weight, on sieves; particle Ø, mm														Silt	Cu
6
7		45.0 - 31.5	31.5 - 22.4	22.4 - 16.0	16.0 - 11.2	11.2 - 8.0	8.0 - 6.3	6.3 - 4.0	4.0 - 2.0	2.0 - 1.0	1.0 - 0.63	0.63 - 0.425	0.425 - 0.2	0.2 - 0.125	0.125 - 0.063	0.063 -
8	Lab. No.	31.5	22.4	16.0	11.2	8.0	6.3	4.0	2.0	1.0	0.63	0.425	0.2	0.125	0.063	0.002
9	440G322	#N/A	#N/A	#N/A	#N/A	#N/A	#N/A	#N/A	#N/A	#N/A	#N/A	#N/A	#N/A	#N/A	26.0	#N/A
10	441G330	#N/A	#N/A	#N/A	#N/A	#N/A	#N/A	#N/A	#N/A	#N/A	#N/A	#N/A	#N/A	#N/A	25.6	#N/A
11	442G335	#N/A	#N/A	#N/A	#N/A	#N/A	#N/A	#N/A	#N/A	#N/A	#N/A	#N/A	#N/A	#N/A	22.8	#N/A
12	443P344	#N/A	#N/A	#N/A	#N/A	#N/A	#N/A	#N/A	#N/A	#N/A	#N/A	#N/A	#N/A	#N/A	1.2	#N/A
13	421C241	#N/A	#N/A	#N/A	#N/A	#N/A	#N/A	#N/A	#N/A	#N/A	#N/A	#N/A	#N/A	#N/A	2.6	#N/A
14	410P199	#N/A	#N/A	#N/A	#N/A	#N/A	#N/A	#N/A	#N/A	#N/A	#N/A	#N/A	#N/A	#N/A	0.7	#N/A
15	410P193	#N/A	#N/A	#N/A	#N/A	#N/A	#N/A	#N/A	#N/A	#N/A	#N/A	#N/A	#N/A	#N/A	13.6	#N/A
16	410P194	#N/A	#N/A	#N/A	#N/A	#N/A	#N/A	#N/A	#N/A	#N/A	#N/A	#N/A	#N/A	#N/A	18.4	#N/A
17	410P195	#N/A	#N/A	#N/A	#N/A	#N/A	#N/A	#N/A	#N/A	#N/A	#N/A	#N/A	#N/A	#N/A	10.4	#N/A
Data

Cell Formulas
Range	Formula
C9:P17	C9	=INDEX(Data_iso,MATCH($B9,Lab.Nr,0),MATCH(Sieve_size_data,Sieves_size_ISO,0))
Q9:Q17	Q9	=100-SUM(C9:P9)

Named Ranges
Name	Refers To	Cells
Data_iso	=ISO!$AC$4:$AP$12	C9:P17
Lab.Nr	=ISO!$C$4:$C$12	C9:P17
Sieve_size_data	=Data!$C$7:$P$7	C9:C17
Sieves_size_ISO	=ISO!$AC$3:$AP$3	C9:P17

Here is Sheet ISO

example2.xlsx
	A	B	C	D	E	F	G																				AA	AB	AC	AD	AE	AF	AG	AH	AI	AJ	AK	AL	AM	AN	AO	AP	AQ	AR
1	1	2	3	4	5	6	7																				27	28	29	30	31	32	33	34	35	36	37	38	39	40	41	42	43	44
2						Coarse fracture																					Residue, % by weight, on sieves; particle Ø, mm
3	Datums	Choose fracture	Lab. Nr.	Soil weight, g	After washing, g	> 4.0 mm	< 4.0 mm																				90.0-63.0	63.0-45.0	45.0-31.5	31.5-22.4	22.4-16.0	16.0-11.2	11.2-8.0	8.0-6.3	6.3-4.0	4.0-2.0	2.0-1.0	1.0-0.63	0.63-0.425	0.425-0.2	0.2-0.125	0.125 - 0.063	<0,063	Passing 63 mm, rupjai frakcijai
4			440G322	200.77	181.92																																0.1	0.1	0.4	16.0	47.0	26.0	10.4	#VALUE!
5			441G330	201.48	124.05																														2.8	0.7	1.5	1.0	0.4	4.4	24.6	25.6	39.0	#VALUE!
6			442G335	202.35	137.56																															0.1	0.3	0.3	0.3	10.7	32.3	22.8	33.2	#VALUE!
7		R.f.	443P344	990.45	896.17	604.71	200																							4.0	4.9	9.5	6.8	4.2	9.9	7.8	8.3	6.3	7.6	16.6	3.5	1.2	9.5	94.3
8		R.f.	421C241	3378	1399.87	1123.04	200																						2.3		1.4	1.1	2.1	1.3	2.5	2.4	2.9	2.3	3.0	10.4	7.1	2.6	58.6	1978.1
9		R.f.	410P199	1511.65	1250.75	908.79	200																								5.2	6.0	6.6	4.8	6.3	9.2	16.3	11.8	7.5	6.6	1.7	0.7	17.3	260.9
10			410P193	200.57	101.54																														0.1	0.2	0.6	0.6	1.4	16.2	17.0	13.6	50.3	#VALUE!
11			410P194	201.82	63.46																																		0.1	1.1	10.2	18.4	70.2	#VALUE!
12			410P195	200.72	174.81																																	0.1	0.1	35.2	40.8	10.4	13.4	#VALUE!
13
ISO

Cell Formulas
Range	Formula
F4:F12	F4	=IF(B4="R.f.",(E4-SUM(H4:O4))," ")
G4:G12	G4	=IF(B4="R.f.","200"," ")
AA4:AH12	AA4	=ROUND(H4/$D4*100,1)
AI4:AP12	AI4	=IF($B4="R.f.", ROUND(P4/SUM($P4:$W4)*(100-SUM($AA4:$AH4)-($D4-$F4-SUM($H4:$O4))/$D4*100),1), ROUND(P4/$D4*100,1))
AQ4:AQ12	AQ4	=IF(B4="R.f.",ROUND(((D4-F4-SUM($H4:$O4))/D4*100),1), ROUND(100-(SUM(AA4:AP4)),1))
AR4:AR12	AR4	=ROUND(D4-F4-SUM($H4:$O4),1)

Named Ranges
Name	Refers To	Cells
Data_iso	=ISO!$AC$4:$AP$12	AI4:AQ4

Cells with Data Validation
Cell	Allow	Criteria
B4:B12	List	=Dropdownlist!$A$2:$A$3

 

[maabadi]

Change formula at C9 to This:

=INDEX(Data_iso,MATCH($B9,Lab.Nr,0),MATCH(C$7,Sieves_size_ISO,0))

 

[inese_green]

Still it shows values only in last column!
I'm not sure if I have putted correct $ when I was created Named ranges.
Have tried to use formula without Named ranges, doesn't work either.

 

[maabadi]

This is YOur Data Sheet. I only change Range for Names Check them.

Book1.xlsm
	A	B	C	D	E	F	G	H																		Z	AA	AB	AC	AD	AE	AF	AG	AH	AI	AJ	AK	AL	AM	AN	AO	AP	AQ	AR
1	1	2	3	4	5	6	7																				27	28	29	30	31	32	33	34	35	36	37	38	39	40	41	42	43	44
2						Coarse fracture																					Residue, % by weight, on sieves; particle Ø, mm
3	Datums	Choose fracture	Lab. Nr.	Soil weight, g	After washing, g	> 4.0 mm	< 4.0 mm																				90.0-63.0	63.0-45.0	45.0-31.5	31.5-22.4	22.4-16.0	16.0-11.2	11.2-8.0	8.0-6.3	6.3-4.0	4.0-2.0	2.0-1.0	1.0-0.63	0.63-0.425	0.425-0.2	0.2-0.125	0.125 - 0.063	<0,063	Passing 63 mm, rupjai frakcijai
4	4		440G322	200.77	181.92																																0.1	0.1	0.4	16	47	26	10.4	#VALUE!
5	5		441G330	201.48	124.05																														2.8	0.7	1.5	1	0.4	4.4	24.6	25.6	39	#VALUE!
6	6		442G335	202.35	137.56																															0.1	0.3	0.3	0.3	10.7	32.3	22.8	33.2	#VALUE!
7	7	R.f.	443P344	990.45	896.17	604.71	200																							4	4.9	9.5	6.8	4.2	9.9	7.8	8.3	6.3	7.6	16.6	3.5	1.2	9.5	94.3
8	8	R.f.	421C241	3378	1399.87	1123.04	200																						2.3		1.4	1.1	2.1	1.3	2.5	2.4	2.9	2.3	3	10.4	7.1	2.6	58.6	1978.1
9	9	R.f.	410P199	1511.65	1250.75	908.79	200																								5.2	6	6.6	4.8	6.3	9.2	16.3	11.8	7.5	6.6	1.7	0.7	17.3	260.9
10	10		410P193	200.57	101.54																														0.1	0.2	0.6	0.6	1.4	16.2	17	13.6	50.3	#VALUE!
11	11		410P194	201.82	63.46																																		0.1	1.1	10.2	18.4	70.2	#VALUE!
12	12		410P195	200.72	174.81																																	0.1	0.1	35.2	40.8	10.4	13.4	#VALUE!
13	13
14
ISO

And this is ISO sheet. One thing copy all Seize size range from data sheet to ISO sheet to you don't have misstyping in them.

Book1.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R
1					lab.Nr.
2					Sieve size
3
4
5		Lab. Nr.	Residue, % by weight, on sieves; particle Ø, mm														Silt	Cu
6
7			45.0-31.5	31.5-22.4	22.4-16.0	16.0-11.2	11.2-8.0	8.0-6.3	6.3-4.0	4.0-2.0	2.0-1.0	1.0-0.63	0.63-0.425	0.425-0.2	0.2-0.125	0.125 - 0.063	<0,063
8		Lab. No.	31.5	22.4	16	11.2	8	6.3	4	2	1	0.63	0.425	0.2	0.125	0.063	0.002
9		440G322	0	0	0	0	0	0	0	0	0.1	0.1	0.4	16	47	26	10.4
10		441G330	0	0	0	0	0	0	2.8	0.7	1.5	1	0.4	4.4	24.6	25.6	39
11		442G335	0	0	0	0	0	0	0	0.1	0.3	0.3	0.3	10.7	32.3	22.8	33.2
12		443P344	0	4	4.9	9.5	6.8	4.2	9.9	7.8	8.3	6.3	7.6	16.6	3.5	1.2	9.5
13		421C241	2.3	0	1.4	1.1	2.1	1.3	2.5	2.4	2.9	2.3	3	10.4	7.1	2.6	58.6
14		410P199	0	0	5.2	6	6.6	4.8	6.3	9.2	16.3	11.8	7.5	6.6	1.7	0.7	17.3
15		410P193	0	0	0	0	0	0	0.1	0.2	0.6	0.6	1.4	16.2	17	13.6	50.3
16		410P194	0	0	0	0	0	0	0	0	0	0	0.1	1.1	10.2	18.4	70.2
17		410P195	0	0	0	0	0	0	0	0	0	0.1	0.1	35.2	40.8	10.4	13.4
18
19
Data

Cell Formulas
Range	Formula
C9:Q17	C9	=INDEX(Data_iso,MATCH($B9,Lab.Nr,0),MATCH(C$7,Sieves_size_ISO,0))

 

[maabadi]

Also I forgot to Show you Changes at Name ranges. View at the end of this posts

Book1.xlsm
	A	B	C	D	E	F	G	H																		Z	AA	AB	AC	AD	AE	AF	AG	AH	AI	AJ	AK	AL	AM	AN	AO	AP	AQ	AR
1	1	2	3	4	5	6	7																				27	28	29	30	31	32	33	34	35	36	37	38	39	40	41	42	43	44
2						Coarse fracture																					Residue, % by weight, on sieves; particle Ø, mm
3	Datums	Choose fracture	Lab. Nr.	Soil weight, g	After washing, g	> 4.0 mm	< 4.0 mm																				90.0-63.0	63.0-45.0	45.0-31.5	31.5-22.4	22.4-16.0	16.0-11.2	11.2-8.0	8.0-6.3	6.3-4.0	4.0-2.0	2.0-1.0	1.0-0.63	0.63-0.425	0.425-0.2	0.2-0.125	0.125 - 0.063	<0,063	Passing 63 mm, rupjai frakcijai
4	4		440G322	200.77	181.92																																0.1	0.1	0.4	16	47	26	10.4	#VALUE!
5	5		441G330	201.48	124.05																														2.8	0.7	1.5	1	0.4	4.4	24.6	25.6	39	#VALUE!
6	6		442G335	202.35	137.56																															0.1	0.3	0.3	0.3	10.7	32.3	22.8	33.2	#VALUE!
7	7	R.f.	443P344	990.45	896.17	604.71	200																							4	4.9	9.5	6.8	4.2	9.9	7.8	8.3	6.3	7.6	16.6	3.5	1.2	9.5	94.3
8	8	R.f.	421C241	3378	1399.87	1123.04	200																						2.3		1.4	1.1	2.1	1.3	2.5	2.4	2.9	2.3	3	10.4	7.1	2.6	58.6	1978.1
9	9	R.f.	410P199	1511.65	1250.75	908.79	200																								5.2	6	6.6	4.8	6.3	9.2	16.3	11.8	7.5	6.6	1.7	0.7	17.3	260.9
10	10		410P193	200.57	101.54																														0.1	0.2	0.6	0.6	1.4	16.2	17	13.6	50.3	#VALUE!
11	11		410P194	201.82	63.46																																		0.1	1.1	10.2	18.4	70.2	#VALUE!
12	12		410P195	200.72	174.81																																	0.1	0.1	35.2	40.8	10.4	13.4	#VALUE!
13	13
14
ISO

Book1.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R
1					lab.Nr.
2					Sieve size
3
4
5		Lab. Nr.	Residue, % by weight, on sieves; particle Ø, mm														Silt	Cu
6
7			45.0-31.5	31.5-22.4	22.4-16.0	16.0-11.2	11.2-8.0	8.0-6.3	6.3-4.0	4.0-2.0	2.0-1.0	1.0-0.63	0.63-0.425	0.425-0.2	0.2-0.125	0.125 - 0.063	<0,063
8		Lab. No.	31.5	22.4	16	11.2	8	6.3	4	2	1	0.63	0.425	0.2	0.125	0.063	0.002
9		440G322	0	0	0	0	0	0	0	0	0	0.1	0.4	16	47	26	10.4
10		441G330	0	0	0	0	0	0	2.8	0.7	3.5	1	0.4	4.4	24.6	25.6	39
11		442G335	0	0	0	0	0	0	0	0.1	0.1	0.3	0.3	10.7	32.3	22.8	33.2
12		443P344	0	4	4.9	9.5	6.8	4.2	9.9	7.8	38.2	6.3	7.6	16.6	3.5	1.2	9.5
13		421C241	2.3	0	1.4	1.1	2.1	1.3	2.5	2.4	9.4	2.3	3	10.4	7.1	2.6	58.6
14		410P199	0	0	5.2	6	6.6	4.8	6.3	9.2	32.9	11.8	7.5	6.6	1.7	0.7	17.3
15		410P193	0	0	0	0	0	0	0.1	0.2	0.3	0.6	1.4	16.2	17	13.6	50.3
16		410P194	0	0	0	0	0	0	0	0	0	0	0.1	1.1	10.2	18.4	70.2
17		410P195	0	0	0	0	0	0	0	0	0	0.1	0.1	35.2	40.8	10.4	13.4
18
Data

Cell Formulas
Range	Formula
C17:Q17,L9:Q16,C9:J16	C9	=INDEX(Data_iso,MATCH($B9,Lab.Nr,0),MATCH(C$7,Sieves_size_ISO,0))

Named Ranges
Name	Refers To	Cells
Data_iso	=ISO!$AA$4:$AQ$12	L9:Q17, C9:J17, K17
Lab.Nr	=ISO!$C$4:$C$12	L9:Q17, C9:J17, K17
Sieves_size_ISO	=ISO!$AA$3:$AQ$3	L9:Q17, C9:J17, K17

 

[inese_green]

Greta, the Sieve size formation was the issue! Really appreciate your help with this!!! Thank YOU!!

 

[maabadi]

You're Welcome & Thanks for Feedback.