Get values from other sheet base on two criteria

inese_green

New Member
Joined
Apr 21, 2021
Messages
3
Office Version
  1. 2016
  2. 2013
Platform
  1. Windows
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
BCDEFGHIJKLMNOPQRS
1lab.Nr.
2Sieve size
3
4
5Lab. Nr. Residue, % by weight, on sieves; particle Ø, mm SiltCu
6
745.0 - 31.5 31.5 - 22.422.4 - 16.016.0 - 11.211.2 - 8.08.0 - 6.36.3 - 4.04.0 - 2.02.0 - 1.01.0 - 0.630.63 - 0.4250.425 - 0.20.2 - 0.1250.125 - 0.0630.063 -
8Lab. No. 31.522.416.011.28.06.34.02.01.00.630.4250.20.1250.0630.002
9440G322#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/A26.0#N/A
10441G330#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/A25.6#N/A
11442G335#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/A22.8#N/A
12443P344#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/A1.2#N/A
13421C241#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/A2.6#N/A
14410P199#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/A0.7#N/A
15410P193#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/A13.6#N/A
16410P194#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/A18.4#N/A
17410P195#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/A10.4#N/A
Data
Cell Formulas
RangeFormula
C9:P17C9=INDEX(Data_iso,MATCH($B9,Lab.Nr,0),MATCH(Sieve_size_data,Sieves_size_ISO,0))
Q9:Q17Q9=100-SUM(C9:P9)
Named Ranges
NameRefers ToCells
Data_iso=ISO!$AC$4:$AP$12C9:P17
Lab.Nr=ISO!$C$4:$C$12C9:P17
Sieve_size_data=Data!$C$7:$P$7C9:C17
Sieves_size_ISO=ISO!$AC$3:$AP$3C9:P17


Here is Sheet ISO
example2.xlsx
ABCDEFGAAABACADAEAFAGAHAIAJAKALAMANAOAPAQAR
11234567272829303132333435363738394041424344
2Coarse fracture Residue, % by weight, on sieves; particle Ø, mm
3DatumsChoose fractureLab. Nr.Soil weight, gAfter washing, g> 4.0 mm< 4.0 mm90.0-63.063.0-45.045.0-31.531.5-22.422.4-16.016.0-11.211.2-8.08.0-6.36.3-4.04.0-2.02.0-1.01.0-0.630.63-0.4250.425-0.20.2-0.1250.125 - 0.063<0,063Passing 63 mm, rupjai frakcijai
4440G322200.77181.92           0.10.10.416.047.026.010.4#VALUE!
5441G330201.48124.05         2.80.71.51.00.44.424.625.639.0#VALUE!
6442G335202.35137.56          0.10.30.30.310.732.322.833.2#VALUE!
7R.f.443P344990.45896.17604.71200   4.04.99.56.84.29.97.88.36.37.616.63.51.29.594.3
8R.f.421C24133781399.871123.04200  2.3 1.41.12.11.32.52.42.92.33.010.47.12.658.61978.1
9R.f.410P1991511.651250.75908.79200    5.26.06.64.86.39.216.311.87.56.61.70.717.3260.9
10410P193200.57101.54         0.10.20.60.61.416.217.013.650.3#VALUE!
11410P194201.8263.46             0.11.110.218.470.2#VALUE!
12410P195200.72174.81            0.10.135.240.810.413.4#VALUE!
13
ISO
Cell Formulas
RangeFormula
F4:F12F4=IF(B4="R.f.",(E4-SUM(H4:O4))," ")
G4:G12G4=IF(B4="R.f.","200"," ")
AA4:AH12AA4=ROUND(H4/$D4*100,1)
AI4:AP12AI4=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:AQ12AQ4=IF(B4="R.f.",ROUND(((D4-F4-SUM($H4:$O4))/D4*100),1), ROUND(100-(SUM(AA4:AP4)),1))
AR4:AR12AR4=ROUND(D4-F4-SUM($H4:$O4),1)
Named Ranges
NameRefers ToCells
Data_iso=ISO!$AC$4:$AP$12AI4:AQ4
Cells with Data Validation
CellAllowCriteria
B4:B12List=Dropdownlist!$A$2:$A$3
 

Excel Facts

Create a chart in one keystroke
Select the data and press Alt+F1 to insert a default chart. You can change the default chart to any chart type

maabadi

Well-known Member
Joined
Oct 22, 2012
Messages
1,924
Office Version
  1. 2019
  2. 2016
Platform
  1. Windows
Change formula at C9 to This:
Excel Formula:
=INDEX(Data_iso,MATCH($B9,Lab.Nr,0),MATCH(C$7,Sieves_size_ISO,0))
 

inese_green

New Member
Joined
Apr 21, 2021
Messages
3
Office Version
  1. 2016
  2. 2013
Platform
  1. Windows
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

Well-known Member
Joined
Oct 22, 2012
Messages
1,924
Office Version
  1. 2019
  2. 2016
Platform
  1. Windows
This is YOur Data Sheet. I only change Range for Names Check them.
Book1.xlsm
ABCDEFGHZAAABACADAEAFAGAHAIAJAKALAMANAOAPAQAR
11234567272829303132333435363738394041424344
2Coarse fracture Residue, % by weight, on sieves; particle Ø, mm
3DatumsChoose fractureLab. Nr.Soil weight, gAfter washing, g> 4.0 mm< 4.0 mm90.0-63.063.0-45.045.0-31.531.5-22.422.4-16.016.0-11.211.2-8.08.0-6.36.3-4.04.0-2.02.0-1.01.0-0.630.63-0.4250.425-0.20.2-0.1250.125 - 0.063<0,063Passing 63 mm, rupjai frakcijai
44440G322200.77181.920.10.10.416472610.4#VALUE!
55441G330201.48124.052.80.71.510.44.424.625.639#VALUE!
66442G335202.35137.560.10.30.30.310.732.322.833.2#VALUE!
77R.f.443P344990.45896.17604.7120044.99.56.84.29.97.88.36.37.616.63.51.29.594.3
88R.f.421C24133781399.871123.042002.31.41.12.11.32.52.42.92.3310.47.12.658.61978.1
99R.f.410P1991511.651250.75908.792005.266.64.86.39.216.311.87.56.61.70.717.3260.9
1010410P193200.57101.540.10.20.60.61.416.21713.650.3#VALUE!
1111410P194201.8263.460.11.110.218.470.2#VALUE!
1212410P195200.72174.810.10.135.240.810.413.4#VALUE!
1313
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
ABCDEFGHIJKLMNOPQR
1lab.Nr.
2Sieve size
3
4
5Lab. Nr. Residue, % by weight, on sieves; particle Ø, mm SiltCu
6
745.0-31.531.5-22.422.4-16.016.0-11.211.2-8.08.0-6.36.3-4.04.0-2.02.0-1.01.0-0.630.63-0.4250.425-0.20.2-0.1250.125 - 0.063<0,063
8Lab. No. 31.522.41611.286.34210.630.4250.20.1250.0630.002
9440G322000000000.10.10.416472610.4
10441G3300000002.80.71.510.44.424.625.639
11442G33500000000.10.30.30.310.732.322.833.2
12443P344044.99.56.84.29.97.88.36.37.616.63.51.29.5
13421C2412.301.41.12.11.32.52.42.92.3310.47.12.658.6
14410P199005.266.64.86.39.216.311.87.56.61.70.717.3
15410P1930000000.10.20.60.61.416.21713.650.3
16410P19400000000000.11.110.218.470.2
17410P1950000000000.10.135.240.810.413.4
18
19
Data
Cell Formulas
RangeFormula
C9:Q17C9=INDEX(Data_iso,MATCH($B9,Lab.Nr,0),MATCH(C$7,Sieves_size_ISO,0))
 

maabadi

Well-known Member
Joined
Oct 22, 2012
Messages
1,924
Office Version
  1. 2019
  2. 2016
Platform
  1. Windows

ADVERTISEMENT

Also I forgot to Show you Changes at Name ranges. View at the end of this posts
Book1.xlsm
ABCDEFGHZAAABACADAEAFAGAHAIAJAKALAMANAOAPAQAR
11234567272829303132333435363738394041424344
2Coarse fracture Residue, % by weight, on sieves; particle Ø, mm
3DatumsChoose fractureLab. Nr.Soil weight, gAfter washing, g> 4.0 mm< 4.0 mm90.0-63.063.0-45.045.0-31.531.5-22.422.4-16.016.0-11.211.2-8.08.0-6.36.3-4.04.0-2.02.0-1.01.0-0.630.63-0.4250.425-0.20.2-0.1250.125 - 0.063<0,063Passing 63 mm, rupjai frakcijai
44440G322200.77181.920.10.10.416472610.4#VALUE!
55441G330201.48124.052.80.71.510.44.424.625.639#VALUE!
66442G335202.35137.560.10.30.30.310.732.322.833.2#VALUE!
77R.f.443P344990.45896.17604.7120044.99.56.84.29.97.88.36.37.616.63.51.29.594.3
88R.f.421C24133781399.871123.042002.31.41.12.11.32.52.42.92.3310.47.12.658.61978.1
99R.f.410P1991511.651250.75908.792005.266.64.86.39.216.311.87.56.61.70.717.3260.9
1010410P193200.57101.540.10.20.60.61.416.21713.650.3#VALUE!
1111410P194201.8263.460.11.110.218.470.2#VALUE!
1212410P195200.72174.810.10.135.240.810.413.4#VALUE!
1313
14
ISO


Book1.xlsm
ABCDEFGHIJKLMNOPQR
1lab.Nr.
2Sieve size
3
4
5Lab. Nr. Residue, % by weight, on sieves; particle Ø, mm SiltCu
6
745.0-31.531.5-22.422.4-16.016.0-11.211.2-8.08.0-6.36.3-4.04.0-2.02.0-1.01.0-0.630.63-0.4250.425-0.20.2-0.1250.125 - 0.063<0,063
8Lab. No. 31.522.41611.286.34210.630.4250.20.1250.0630.002
9440G3220000000000.10.416472610.4
10441G3300000002.80.73.510.44.424.625.639
11442G33500000000.10.10.30.310.732.322.833.2
12443P344044.99.56.84.29.97.838.26.37.616.63.51.29.5
13421C2412.301.41.12.11.32.52.49.42.3310.47.12.658.6
14410P199005.266.64.86.39.232.911.87.56.61.70.717.3
15410P1930000000.10.20.30.61.416.21713.650.3
16410P19400000000000.11.110.218.470.2
17410P1950000000000.10.135.240.810.413.4
18
Data
Cell Formulas
RangeFormula
C17:Q17,L9:Q16,C9:J16C9=INDEX(Data_iso,MATCH($B9,Lab.Nr,0),MATCH(C$7,Sieves_size_ISO,0))
Named Ranges
NameRefers ToCells
Data_iso=ISO!$AA$4:$AQ$12L9:Q17, C9:J17, K17
Lab.Nr=ISO!$C$4:$C$12L9:Q17, C9:J17, K17
Sieves_size_ISO=ISO!$AA$3:$AQ$3L9:Q17, C9:J17, K17
 
Solution

inese_green

New Member
Joined
Apr 21, 2021
Messages
3
Office Version
  1. 2016
  2. 2013
Platform
  1. Windows
Greta, the Sieve size formation was the issue! Really appreciate your help with this!!! Thank YOU!!
 

maabadi

Well-known Member
Joined
Oct 22, 2012
Messages
1,924
Office Version
  1. 2019
  2. 2016
Platform
  1. Windows
You're Welcome & Thanks for Feedback.
 

Watch MrExcel Video

Forum statistics

Threads
1,132,640
Messages
5,654,535
Members
418,138
Latest member
agnesegras

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top