Look Up Table Request for Help

[RailEngineer76]

Hi,
I have a set of data I want to compare to a table of set criteria but cant fathom out how to get a cell to bring back the right answer.
Basically, we have criteria for "track categories" on types of rail and sleepers allowed, I then need to see if the types recorded meet the criteria set out.

Let me describe that better (refer to attached mini-sheet):

The criteria is set out in Col Q to S (Row 5 to 21) for Track Category, Rail, and Sleepers.
The data to check against these are for the set Track Category (Col J) with rail and sleeper types for each track [tracks are identified as "1100" and "2100", they are different tracks/roads]. So, for the "1100" the track category in Col J has rail type in Col F and sleeper type in Col H; for the "2100" has track category in Col J as well, but rail type in Col G and sleeper type in Col I. All to be reviewed against the criteria in Col Q to S.
If the rail type and sleeper type match the criteria for the proposed track category then its compliant (a yes or tick) if not then its non-compliant (a not or cross).

Any ideas how to do this?

added mini-sheet and a image of the sheet to help.


Cheers,
Dave

Standard Compliance.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T
1
2	ELR	START MILEAGE		END MILEAGE		RAIL TYPE		SLEEPER TYPE		Proposed Track Category	Compliant Rail	Compliant Sleeper	Compliant to Table 18
3		Miles	Yards	Miles	Yards	1100	2100	1100	2100				1100	2100			Table 18 Variants
4	MVL3	8	0	8	110	2	2	C2	C2	2							Track Cat	Rail Type	Sleeper Type
5	MVL3	8	110	8	220	1	1	C2	C2	2							1A	3	C2
6	MVL3	8	220	8	330	2	2	T	C2	2							1	3	C2
7	MVL3	8	330	8	440	1	2	T	C2	2							2	3	C2
8	MVL3	8	440	8	550	1	2	T	C1	2							2	2	C1
9	MVL3	8	550	8	660	2	2	T	C1	2							3	3	C2
10	MVL3	8	660	8	770	2	2	T	C1	2							3	2	C1
11	MVL3	8	770	8	880	2	2	T	C1	2							3	2	S
12	MVL3	8	880	8	990	2	2	T	C1	2							4	3	C2
13	MVL3	8	990	8	1100	2	2	T	C1	1							4	2	C1
14	MVL3	8	1100	8	1210	2	2	C1	T	1							4	2	S
15	MVL3	8	1210	8	1320	2	2	C1	T	1							5	3	C2
16	MVL3	8	1320	8	1430	2	2	C1	C1	1							5	2	C1
17	MVL3	8	1430	8	1540	2	2	C1	C1	1							5	2	S
18	MVL3	8	1540	8	1650	2	2	C1	C1	1							6	3	C2
19	MVL3	8	1650	9	0	2	2	C1	C1	1							6	2	C1
20	MVL3	9	0	9	110	2	2	C1	C1	1							6	2	T
21	MVL3	9	110	9	220	2	2	C1	C1	1							6	2	S
22	MVL3	9	220	9	330	2	2	C1	C1	1
23	MVL3	9	330	9	440	2	2	C1	C1	1
24	MVL3	9	440	9	550	2	2	C1	C1	1
25	MVL3	9	550	9	660	2	2	C1	C1	1
26	MVL3	9	660	9	770	2	2	C1	T	1
27	MVL3	9	770	9	880	2	1	T	T	1
Standard Construction

Cell Formulas
Range	Formula
A4	A4	='C:\Users\Wools\AppData\Local\Temp\Temp1_Excel.zip\[MVL3 TARQUIN P03.xlsx]Version'!C4
F4:G27	F4	='C:\Users\Wools\AppData\Local\Temp\Temp1_Excel.zip\[MVL3 TARQUIN P03.xlsx]Asset Data'!X6
H4:I27	H4	='C:\Users\Wools\AppData\Local\Temp\Temp1_Excel.zip\[MVL3 TARQUIN P03.xlsx]Asset Data'!AD6
A5:A27	A5	=A4
B5:C27	B5	=D4
D5:D27	D5	=IF(E5="0",D4+1,D4)
E5:E27	E5	=IF(E4=1650,"0",E4+110)

 

[Skybluekid]

Try this

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T
1	ELR	START MILEAGE		END MILEAGE		RAIL TYPE		SLEEPER TYPE		Proposed Track Category	Compliant Rail	Compliant Sleeper	Compliant to Table 18
2		Miles	Yards	Miles	Yards	1100	2100	1100	2100				1100	2100
3	MVL3	8	0	8	110	2	2	C2	C2	2			Non Compliant					Table 18 Variants
4	MVL3	8	110	8	220	1	1	C2	C2	2			Non Compliant				Track Cat	Rail Type	Sleeper Type
5	MVL3	8	220	8	330	2	2	T	C2	2			Non Compliant				1A	3	C2
6	MVL3	8	330	8	440	1	2	T	C2	2			Non Compliant				1	3	C2
7	MVL3	8	440	8	550	1	2	T	C1	2			Compliant				2	3	C2
8	MVL3	8	550	8	660	2	2	T	C1	2			Compliant				2	2	C1
9	MVL3	8	660	8	770	2	2	T	C1	2			Compliant				3	3	C2
10	MVL3	8	770	8	880	2	2	T	C1	2			Compliant				3	2	C1
11	MVL3	8	880	8	990	2	2	T	C1	2			Compliant				3	2	S
12	MVL3	8	990	8	1100	2	2	T	C1	1			Compliant				4	3	C2
13	MVL3	8	1100	8	1210	2	2	C1	T	1			Compliant				4	2	C1
14	MVL3	8	1210	8	1320	2	2	C1	T	1			Compliant				4	2	S
15	MVL3	8	1320	8	1430	2	2	C1	C1	1			Compliant				5	3	C2
16	MVL3	8	1430	8	1540	2	2	C1	C1	1			Compliant				5	2	C1
17	MVL3	8	1540	8	1650	2	2	C1	C1	1			Compliant				5	2	S
18	MVL3	8	1650	9	0	2	2	C1	C1	1			Compliant				6	3	C2
19	MVL3	9	0	9	110	2	2	C1	C1	1			Compliant				6	2	C1
20	MVL3	9	110	9	220	2	2	C1	C1	1			Compliant				6	2	T
21	MVL3	9	220	9	330	2	2	C1	C1	1			Compliant				6	2	S
22	MVL3	9	330	9	440	2	2	C1	C1	1			Compliant
23	MVL3	9	440	9	550	2	2	C1	C1	1			Compliant
24	MVL3	9	550	9	660	2	2	C1	C1	1			Compliant
25	MVL3	9	660	9	770	2	2	C1	T	1			Compliant
26	MVL3	9	770	9	880	2	1	T	T	1			Non Compliant
Sheet6

Cell Formulas
Range	Formula
M3:M26	M3	=IFERROR(IF(INDEX($Q$5:$Q$21,MATCH(G3&I3,$R$5:$R$21&$S$5:$S$21,0))=INDEX($Q$5:$Q$21,MATCH(G3&I3,$R$5:$R$21&$S$5:$S$21,0)),"Compliant"),"Non Compliant")

 

[RailEngineer76]

Hi Skybluekid,

Almost - its not taking the Col J into consideration.

It needs to see whats allowed for Col J (Track Cat) in the Table (Col Q-R-S, against Col Q) to see if the values in Col F and H (for Col M) and G and I (for Col N) are related to that value in the table Col Q-R-S.

Sorry, hope that makes sense?


Dave

 

[Skybluekid]

So to make sure I am on the right lines(Sorry about the pun!) IF Col F & Col H & Col J = Col G & Col I & Col J, then it is compliant, if not it is not compliant?

 

[Skybluekid]

Try this:

Book1.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T
1	ELR	START MILEAGE		END MILEAGE		RAIL TYPE		SLEEPER TYPE		Proposed Track Category	Compliant Rail	Compliant Sleeper	Compliant to Table 18
2		Miles	Yards	Miles	Yards	1100	2100	1100	2100				1100	2100
3	MVL3	8	0	8	110	2	2	C2	C2	2	Non Compliant	Non Compliant					Table 18 Variants
4	MVL3	8	110	8	220	1	1	C2	C2	2	Non Compliant	Non Compliant					Track Cat	Rail Type	Sleeper Type
5	MVL3	8	220	8	330	2	2	T	C2	2	Non Compliant	Non Compliant	6				1A	3	C2
6	MVL3	8	330	8	440	1	2	T	C2	2	Non Compliant	Non Compliant					1	3	C2
7	MVL3	8	440	8	550	1	2	T	C1	2	Non Compliant	Compliant		2			2	3	C2
8	MVL3	8	550	8	660	2	2	T	C1	2	Non Compliant	Compliant	6	2			2	2	C1
9	MVL3	8	660	8	770	2	2	T	C1	2	Non Compliant	Compliant	6	2			3	3	C2
10	MVL3	8	770	8	880	2	2	T	C1	2	Non Compliant	Compliant	6	2			3	2	C1
11	MVL3	8	880	8	990	2	2	T	C1	2	Non Compliant	Compliant	6	2			3	2	S
12	MVL3	8	990	8	1100	2	2	T	C1	1	Non Compliant	Non Compliant	6	2			4	3	C2
13	MVL3	8	1100	8	1210	2	2	C1	T	1	Non Compliant	Non Compliant	2	6			4	2	C1
14	MVL3	8	1210	8	1320	2	2	C1	T	1	Non Compliant	Non Compliant	2	6			4	2	S
15	MVL3	8	1320	8	1430	2	2	C1	C1	1	Non Compliant	Non Compliant	2	2			5	3	C2
16	MVL3	8	1430	8	1540	2	2	C1	C1	1	Non Compliant	Non Compliant	2	2			5	2	C1
17	MVL3	8	1540	8	1650	2	2	C1	C1	1	Non Compliant	Non Compliant	2	2			5	2	S
18	MVL3	8	1650	9	0	2	2	C1	C1	1	Non Compliant	Non Compliant	2	2			6	3	C2
19	MVL3	9	0	9	110	2	2	C1	C1	1	Non Compliant	Non Compliant	2	2			6	2	C1
20	MVL3	9	110	9	220	2	2	C1	C1	1	Non Compliant	Non Compliant	2	2			6	2	T
21	MVL3	9	220	9	330	2	2	C1	C1	1	Non Compliant	Non Compliant	2	2			6	2	S
22	MVL3	9	330	9	440	2	2	C1	C1	1	Non Compliant	Non Compliant	2	2
23	MVL3	9	440	9	550	2	2	C1	C1	1	Non Compliant	Non Compliant	2	2
24	MVL3	9	550	9	660	2	2	C1	C1	1	Non Compliant	Non Compliant	2	2
25	MVL3	9	660	9	770	2	2	C1	T	1	Non Compliant	Non Compliant	2	6
26	MVL3	9	770	9	880	2	1	T	T	1	Non Compliant	Non Compliant	6
Sheet2

Cell Formulas
Range	Formula
K3:K26	K3	=IF(M3=J3,"Compliant","Non Compliant")
L3:L26	L3	=IF(N3=J3,"Compliant","Non Compliant")
M3:N26	M3	=IFERROR(INDEX($Q$5:$Q$21,MATCH(F3&H3,$R$5:$R$21&$S$5:$S$21,0)),"")

 

[RailEngineer76]

So to make sure I am on the right lines(Sorry about the pun!) IF Col F & Col H & Col J = Col G & Col I & Col J, then it is compliant, if not it is not compliant?

Hi, sorry wasn't online over weekend.
No, that's not right.

If Col F & Col H & Col J are found together in Table Q-R-S then its compliant.

Its hard to get the description clear. Basically, for a given Track Cat (Col Q) in Table Q-R-S we are allowed certain types of rail and sleeper (Col R and Col S).

The existing rails and sleepers (Col F and Col H) need to be OK for the Track Cat in Col J against that Table Q-R-S.

Sorry, I may be going in circles trying to describe this now...

 

[Alex Blakenburg]

In cell M4 try either of these:

=IF(
      COUNTIFS($Q$5:$Q$21,J4,
                          $R$5:$R$21,F4,
                          $S$5:$S$21,H4)
       =0,
      "No-Compliant",
      "Compliant")

=IF(
     ISERROR(
           LOOKUP(2, 1/ (($Q$5:$Q$21=J4) * ($R$5:$R$21=F4) *( $S$5:$S$21=H4)), $Q$5:$Q$21)),
     "Non-Compliant",
     "Compliant")

 

[RailEngineer76]

That 2nd formula seems to work a treat.

Thank you for all your help, very much appreciated.

 

[Alex Blakenburg]

Thanks for letting us know. Glad we could help.

 

[smozgur]

That 2nd formula seems to work a treat.

Thank you for all your help, very much appreciated.

The marked solution has been changed accordingly. In your future questions, please mark the post as the solution that actually answered your question, instead of your feedback message as it will help future readers. No further action is required for this thread.