Continuous Dataset to Discrete Dataset - Continued

[ahume12]

Building on the response to a previous thread linked here - I have another query in the sorting of continuous data into discrete data.
I had an excellent solution provided to this previous thread using vlookups to get the correct answer, but the I have developed the data and I cant manage to get this previous solution to work.

Building on my previous dataset, I have build in two categories to my data, which are ELR and LINE.

I would like to undertake the same sorting as before, find the minimum temperature in the TEMP column in the DATA FROM based on a FROM and TO mileage in the DATA TO tab however this time, I would like to return values that take the ELR and LINE categories into account and sorts the response accordingly.

I have tried multiple IF functions and excels newer IFS function, but I cant get the logic of these to work.

Would appreciate any help.

DATA FROM

Sample Data AH.xlsx
	A	B	C	D	E
1	ELR	LINE	From Miles Decimal	To Miles Decimal	Temp
2	BCJ	DN	59.57	60.41	25.2
3	BCJ	DN	60.41	60.66	17.3
4	BCJ	DN	60.66	60.91	21.4
5	BCJ	DN	60.91	61.16	24.6
6	BCJ	UP	59.57	60.41	19
7	BCJ	UP	60.41	60.66	27
8	BCJ	UP	60.66	60.92	28
9	BCJ	UP	60.92	61.16	16.6
10	CJL	DN	111.72	111.97	0
11	CJL	DN	111.97	112.22	0
12	CJL	DN	112.22	112.47	17.4
13	CJL	DN	112.47	112.72	21.9
14	CJL	UP	111.72	111.97	0.0
15	CJL	UP	111.97	112.22	33.3
16	CJL	UP	112.22	112.47	23.0
17	CJL	UP	112.47	112.72	23.8
Data From

DATA TO

Sample Data AH.xlsx
	A	B	C	D	E
1	ELR	LINE	Miles From	Miles To	Min Temp
2	BCJ	DN	59.00	59.25	N/A
3	BCJ	DN	59.25	59.50	N/A
4	BCJ	DN	59.50	59.75	25.20
5	BCJ	DN	59.75	60.00	25.20
6	BCJ	DN	60.00	60.25	25.20
7	BCJ	DN	60.25	60.50	17.30
8	BCJ	DN	60.50	60.75	17.30
9	BCJ	DN	60.75	61.00	21.40
10	BCJ	DN	61.00	61.25	24.60
11	BCJ	UP	59.00	59.25	N/A
12	BCJ	UP	59.25	59.50	N/A
13	BCJ	UP	59.50	59.75	19.00
14	BCJ	UP	59.75	60.00	19.00
15	BCJ	UP	60.00	60.25	19.00
16	BCJ	UP	60.25	60.50	19.00
17	BCJ	UP	60.50	60.75	27.00
18	BCJ	UP	60.75	61.00	27.00
19	BCJ	UP	61.00	61.25	16.60
20	CJL	DN	111.50	111.75	N/A
21	CJL	DN	111.75	112.00	0.00
22	CJL	DN	112.00	112.25	0.00
23	CJL	DN	112.25	112.50	17.40
24	CJL	DN	112.50	112.75	21.90
25	CJL	UP	111.50	111.75	N/A
26	CJL	UP	111.75	112.00	0.00
27	CJL	UP	112.00	112.25	33.30
28	CJL	UP	112.25	112.50	23.00
29	CJL	UP	112.50	112.75	23.80
Data To

 

[Fluff]

How about

+Fluff 1.xlsm
	A	B	C	D	E
1	ELR	LINE	Miles From	Miles To	Min Temp
2	BCJ	DN	59	59.25	N/A
3	BCJ	DN	59.25	59.5	N/A
4	BCJ	DN	59.5	59.75	25.2
5	BCJ	DN	59.75	60	25.2
6	BCJ	DN	60	60.25	25.2
7	BCJ	DN	60.25	60.5	17.3
8	BCJ	DN	60.5	60.75	17.3
9	BCJ	DN	60.75	61	21.4
10	BCJ	DN	61	61.25	24.6
11	BCJ	UP	59	59.25	N/A
12	BCJ	UP	59.25	59.5	N/A
13	BCJ	UP	59.5	59.75	19
14	BCJ	UP	59.75	60	19
15	BCJ	UP	60	60.25	19
16	BCJ	UP	60.25	60.5	19
17	BCJ	UP	60.5	60.75	27
18	BCJ	UP	60.75	61	16.6
19	BCJ	UP	61	61.25	16.6
20	CJL	DN	111.5	111.75	0
21	CJL	DN	111.75	112	0
22	CJL	DN	112	112.25	0
23	CJL	DN	112.25	112.5	17.4
24	CJL	DN	112.5	112.75	21.9
25	CJL	UP	111.5	111.75	0
26	CJL	UP	111.75	112	0
27	CJL	UP	112	112.25	23
28	CJL	UP	112.25	112.5	23
29	CJL	UP	112.5	112.75	23.8
Data

Cell Formulas
Range	Formula
E2:E29	E2	=IFERROR(AGGREGATE(15,6,VLOOKUP(Table2[@[Miles From]:[Miles To]],FILTER(Table1[[From Miles Decimal]:[Temp]],(Table1[ELR]=[@ELR])*(Table1[LINE]=[@LINE])),3),1),"N/A")

 

[ahume12]

This has worked exactly as expected.
This is excellent - thank you very much indeed
I don't pretend to understand it, when I get some time I'll look into the commands. I have never used FILTER before so will definitely have a good look.

 

[Fluff]

You're welcome & thanks for the feedback.