Using indirect with result of lookup value from index/match for another index/match!

[PureBluff]

Hi all,

Think I'm even going to struggle to explain this one!

I pull some data from a couple of databases using SQL, however, the SQL commands available to the end user are limited, so I must do a large part of the work in excel.

Certain products have different days "validation" before they need consumed, this is static based on the product sub group, however, not recorded in the SQL databases.

e.g.
Ingredients: 1 day
Frozen crated product: 3 days
Frozen boxed product: 5 days
etc...

These are stored in a small table in my excel workbook (the table will be much larger when final)

odbcs 070720.xlsm
	D	E	F	G	H
1	Code	Description	Validation Type	Timestamp Column	Validation Days
2	S58	Veg 2 - 17780001	Veg Blends	A	1
3	S57	Veg 1 - 17760001	Veg Blends	A	1
4	8914	NZ LAMB SKIRTS TEMPERING	NZ Tempering	AN	5
5	B8805	95VL OTM STEAK TRIM FROZEN	Frozen Crated	AN	3
6	L8810	UK LAMB SKIRTS FROZEN	Frozen Crated	AN	3
7	8912	NZ 75CL TEMPERING	NZ Tempering	AN	5
8	B8840	80VL OTM VALUE TRIM FROZEN	Frozen Crated	AN	3
9	B8874	95VL FROZEN	Frozen Crated	AN	3
10	B8764	95VL ROI STEAK TRIM FROZEN UTM	Frozen Crated	AN	3
11	B8374	98VL UTM IRISH TRIM FROZEN	Frozen Crated	AN	3
12	B8767	ROI 80VL OTM TRIM FROZEN	Frozen Crated	AN	3
Lookups

I need to lookup up the product code in one of my SQL query result columns against this table to return the column address (A/AN) and then use that value as the indexed column in an index/match formula - in one formula!

e.g

for S58

=INDEX(IMV!A:A,MATCH(A2,IMV!U:U,0))

but for B8805 I would want

=INDEX(IMV!AN:AN,MATCH(A2,IMV!U:U,0))

this value coming from the above indirect/lookup

In short, column O in the below needs to lookup "creation date" for some products and "last modified date" for others, however these are stored in different fields (obviously!) in the SQL db, I need Excel to know which column to look at, by referencing the first table which will be user-maintainable.

odbcs 070720.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S
1	pallet_number	stock_area	stock_rack	stock_location	modified_time	Total_boxes	Total_items	Total_box_label_weig	Max_life	Product	Description	Analysis_Code2	Location	Location2	Orig_Tx	Consumption Date
2	12627	2	A	1	1062711015	12	12	120	738747	S58	Veg 2 - 17780001	FROZEN	2/A/1	A-B Link Tunnel	04/06/2020 10:24	07/06/2020 10:24			04/6/2020 10:33
3	16189	2	A	1	1062711808	3	3	60.87	738229	L8810	UK LAMB SKIRTS FROZEN	FROZEN	2/A/1	A-B Link Tunnel	22/04/2020 12:04	25/04/2020 12:04
4	16212	2	A	1	1062706670	5	5	136	738594	8912	NZ 75CL TEMPERING	FROZEN	2/A/1	A-B Link Tunnel	03/07/2020 15:35	06/07/2020 15:35
Union

I really hope that this makes some sense!

 

[Toadstool]

Hi PureBluff,

There's some sheets missing from your provided data so just let me give a similar example of using INDIRECT.

Here's my test data in the IMV tab:

Book1
	A	B																		T	U	V																	AM	AN
1	This is A1																				Dog																			This is AN1
2	This is A2																				Cat																			This is AN2
3	This is A3																				Pig																			This is AN3
4	This is A4																				Mole																			This is AN4
5	This is A5																				Goat																			This is AN5
6	This is A6																				Sheep																			This is AN6
7	This is A7																				Cow																			This is AN7
8	This is A8																				Horse																			This is AN8
9	This is A9																				Deer																			This is AN9
10	This is A10																				Badger																			This is AN10
IMV

Here's my INDIRECT statement driven by the contents of the Timestamp column G:

Book1
	F	G	H	I
1	Animal	Timestamp		Result
2	Pig	A		This is A3
3	Goat	A		This is A5
4	Mole	AN		This is AN4
5	Goat	AN		This is AN5
Work

Cell Formulas
Range	Formula
I2:I5	I2	=INDEX(INDIRECT("IMV!"&G2&":"&G2),MATCH(F2,IMV!U:U,0))

 

[PureBluff]

Thanks toadstool, will log onto the remote server and have a look.

I didn't post the IMV sheet as it's huge & largely redundant data, coupled with poor excel formatting (I'm pulling all columns in the db so I have a reference point until I have the result set working) - then I'll reduce the SQL select from "select *" to only the ones I need.

 

[PureBluff]

Hi @Toadstool , while the formula works as you suggested, it doesn't work for the additional complexity that I am struggling with; I need to lookup within a lookup for my intended result. I have placed the "looks" data inside the "Union" sheet, so there's less sheets to contend with.

odbcs140720.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC	AD	AE	AF	AG	AH	AI	AJ	AK	AL	AM	AN	AO	AP	AQ	AR	AS	AT	AU	AV	AW	AX	AY
1	Ins_timestamp	Id	Prod_code	From_status	From_ord_type	From_ord_no	From_ord_line	From_site	From_locn	From_pal_ser	From_pal_no	From_batch	From_doc	To_status	To_ord_type	To_ord_no	To_ord_line	To_site	To_locn	To_pal_ser	To_pal_no	To_batch	To_doc	Stk_adj_factor	Move_datetime	Terminal_port	Terminal_IP	Terminal_no	Trans_serial_no	User_code	Team_size	Curr_boxes	Curr_act_measure	Curr_label_kg	Curr_act_kg	Source	Ver_no	ExportStatus	Extra_detail	Trans_datetime	Unit_of_measure	Tare	Label_detail_id	Local_id	Local_site	Date	Type	Location	Site	items	Weight
2	04/06/2020 10:33	890106789	S58	PORDER	2	3008	S58			0	0	0		STOCK	0	0	S58		03/A/001	0	12625	0	GRN110575	1	04/06/2020 10:33	FOPS6-UC/000		97	439806		0	48	480	480	480		7.888	0		04/06/2020 10:22	kg	95.04	163407955			04/06/2020 10:00	AcceptedFromPO	3008		48.00	480.00
3	04/06/2020 11:10	890119469	S58	STOCK	0	0	S58		03/A/001	4544798	12625	0		STOCK	0	0	S58		08/A/004	4544798	12625	0		0	04/06/2020 11:10	FOPS6-UC/000		97	439806		0	48	480	480	480		7.888	0		04/06/2020 10:22	kg	95.04	163407955			04/06/2020 11:00	StockMovement	-		48.00	480.00
4	05/07/2020 03:49	902333190	L8897	STOCK	0	0	L8897		00/ /000	0	0	0		STOCK	0	0	L8897		08/A/015	4574416	16365	0		0	05/07/2020 03:49	FOPS6-UC/000		97	458129	JOZEFW	0	1	27.2	27.2	27.2		7.888	0		05/07/2020 03:47	kg	0.6	165683179			05/07/2020 03:00	StockMovement	-		1.00	27.20
5	05/07/2020 03:49	902333192	L8897	STOCK	0	0	L8897		00/ /000	0	0	0		STOCK	0	0	L8897		08/A/015	4574416	16365	0		0	05/07/2020 03:49	FOPS6-UC/000		97	458130	JOZEFW	0	1	27.2	27.2	27.2		7.888	0		05/07/2020 03:47	kg	0.6	165683180			05/07/2020 03:00	StockMovement	-		1.00	27.20
6	05/07/2020 03:52	902333398	L8897	STOCK	0	0	L8897		08/A/015	4574416	16365	0		STOCK	0	0	L8897		08/A/008	4574416	16365	0		0	05/07/2020 03:52	FOPS6-UC/000		97	458129	JOZEFW	0	1	27.2	27.2	27.2		7.888	0		05/07/2020 03:47	kg	0.6	165683179			05/07/2020 03:00	StockMovement	-		1.00	27.20
7	05/07/2020 03:52	902333399	L8897	STOCK	0	0	L8897		08/A/015	4574416	16365	0		STOCK	0	0	L8897		08/A/008	4574416	16365	0		0	05/07/2020 03:52	FOPS6-UC/000		97	458130	JOZEFW	0	1	27.2	27.2	27.2		7.888	0		05/07/2020 03:47	kg	0.6	165683180			05/07/2020 03:00	StockMovement	-		1.00	27.20
8	05/07/2020 03:52	902333415	L8897	STOCK	0	0	L8897		08/A/015	4574416	16365	0		STOCK	0	0	L8897		08/A/008	4574416	16365	0		0	05/07/2020 03:52	FOPS6-UC/000		97	458146	JOZEFW	0	1	27.2	27.2	27.2		7.888	0		05/07/2020 03:47	kg	0.6	165683196			05/07/2020 03:00	StockMovement	-		1.00	27.20
9	05/07/2020 03:52	902333416	L8897	STOCK	0	0	L8897		08/A/015	4574416	16365	0		STOCK	0	0	L8897		08/A/008	4574416	16365	0		0	05/07/2020 03:52	FOPS6-UC/000		97	458147	JOZEFW	0	1	27.2	27.2	27.2		7.888	0		05/07/2020 03:47	kg	0.6	165683197			05/07/2020 03:00	StockMovement	-		1.00	27.20
10	05/07/2020 03:52	902333417	L8897	STOCK	0	0	L8897		08/A/015	4574416	16365	0		STOCK	0	0	L8897		08/A/008	4574416	16365	0		0	05/07/2020 03:52	FOPS6-UC/000		97	458148	JOZEFW	0	1	27.2	27.2	27.2		7.888	0		05/07/2020 03:47	kg	0.6	165683198			05/07/2020 03:00	StockMovement	-		1.00	27.20
11	05/07/2020 03:52	902333418	L8897	STOCK	0	0	L8897		08/A/015	4574416	16365	0		STOCK	0	0	L8897		08/A/008	4574416	16365	0		0	05/07/2020 03:52	FOPS6-UC/000		97	458149	JOZEFW	0	1	27.2	27.2	27.2		7.888	0		05/07/2020 03:47	kg	0.6	165683199			05/07/2020 03:00	StockMovement	-		1.00	27.20
12	06/07/2020 04:05	902684060	8912	STOCK	0	999997	8912		00/ /000	4575302	203	0		STOCK	0	0	8912		99/A/001	0	16467	0		0	06/07/2020 04:05	FOPS6-UC/000		90	624257	KASPARS	0	1	27.36	27.2	27.36		7.888	0		06/07/2020 03:45	kg	0.6	165747963			06/07/2020 04:00	AssignedToDO	999997		-1.00	-27.36
13	06/07/2020 04:05	902684061	8912	STOCK	0	999997	8912		00/ /000	4575302	203	0		STOCK	0	0	8912		99/A/001	0	16467	0		0	06/07/2020 04:05	FOPS6-UC/000		90	624258	KASPARS	0	1	27.36	27.2	27.36		7.888	0		06/07/2020 03:45	kg	0.6	165747964			06/07/2020 04:00	AssignedToDO	999997		-1.00	-27.36
14	10/07/2020 23:06	904580928	B8805	PORDER	2	149277	B8805			0	0	0		STOCK	0	0	B8805		06/A/001	0	16991	0	GRN111186	1	10/07/2020 23:06	FOPS6-UC/000		100	49577801		0	1	18.48	18.48	18.48	0512880524062031052119062000184889	7.888	0		10/07/2020 23:03	kg	1.58	166111047			10/07/2020 23:00	AcceptedFromPO	149277		1.00	18.48
15	10/07/2020 23:06	904580939	B8805	PORDER	2	149277	B8805			0	0	0		STOCK	0	0	B8805		06/A/001	0	16991	0	GRN111186	1	10/07/2020 23:06	FOPS6-UC/000		100	49577812		0	1	18.94	18.94	18.94	0512880524062031052119062000189489	7.888	0		10/07/2020 23:03	kg	1.58	166111058			10/07/2020 23:00	AcceptedFromPO	149277		1.00	18.94
16	10/07/2020 23:50	904595614	B8805	STOCK	0	0	B8805		06/A/001	4579752	16991	0		STOCK	0	0	B8805		03/A/001	4579752	16991	0		0	10/07/2020 23:50	FOPS6-UC/000		100	49577768		0	1	18.68	18.68	18.68		7.888	0		10/07/2020 23:02	kg	1.58	166111013			10/07/2020 23:00	StockMovement	-		1.00	18.68
17	10/07/2020 23:50	904595615	B8805	STOCK	0	0	B8805		06/A/001	4579752	16991	0		STOCK	0	0	B8805		03/A/001	4579752	16991	0		0	10/07/2020 23:50	FOPS6-UC/000		100	49577769		0	1	19.44	19.44	19.44		7.888	0		10/07/2020 23:02	kg	1.58	166111014			10/07/2020 23:00	StockMovement	-		1.00	19.44
18	10/07/2020 23:50	904595616	B8805	STOCK	0	0	B8805		06/A/001	4579752	16991	0		STOCK	0	0	B8805		03/A/001	4579752	16991	0		0	10/07/2020 23:50	FOPS6-UC/000		100	49577770		0	1	18.8	18.8	18.8		7.888	0		10/07/2020 23:02	kg	1.58	166111015			10/07/2020 23:00	StockMovement	-		1.00	18.80
19	10/07/2020 23:50	904595617	B8805	STOCK	0	0	B8805		06/A/001	4579752	16991	0		STOCK	0	0	B8805		03/A/001	4579752	16991	0		0	10/07/2020 23:50	FOPS6-UC/000		100	49577771		0	1	18.49	18.49	18.49		7.888	0		10/07/2020 23:02	kg	1.58	166111016			10/07/2020 23:00	StockMovement	-		1.00	18.49
20	10/07/2020 23:50	904595658	B8805	STOCK	0	0	B8805		06/A/001	4579752	16991	0		STOCK	0	0	B8805		03/A/001	4579752	16991	0		0	10/07/2020 23:50	FOPS6-UC/000		100	49577812		0	1	18.94	18.94	18.94		7.888	0		10/07/2020 23:03	kg	1.58	166111058			10/07/2020 23:00	StockMovement	-		1.00	18.94
21	11/07/2020 03:10	904647839	B8764	PORDER	2	149270	B8764			0	0	0		STOCK	0	0	B8764		06/A/001	0	17017	0	GRN111200	1	11/07/2020 03:10	FOPS6-UC/000		100	49579901		0	1	18.46	18.46	18.46	0552876422052022052119052000184660	7.888	0		11/07/2020 03:07	kg	1.56	166118912			11/07/2020 03:00	AcceptedFromPO	149270		1.00	18.46
22	11/07/2020 03:10	904647840	B8764	PORDER	2	149270	B8764			0	0	0		STOCK	0	0	B8764		06/A/001	0	17017	0	GRN111200	1	11/07/2020 03:10	FOPS6-UC/000		100	49579902		0	1	18.34	18.34	18.34	0552876425052025052121052000183460	7.888	0		11/07/2020 03:07	kg	1.56	166118913			11/07/2020 03:00	AcceptedFromPO	149270		1.00	18.34
23	11/07/2020 03:10	904647841	B8764	PORDER	2	149270	B8764			0	0	0		STOCK	0	0	B8764		06/A/001	0	17017	0	GRN111200	1	11/07/2020 03:10	FOPS6-UC/000		100	49579903		0	1	17.96	17.96	17.96	0552876425052025052121052000179660	7.888	0		11/07/2020 03:07	kg	1.56	166118914			11/07/2020 03:00	AcceptedFromPO	149270		1.00	17.96
24	11/07/2020 03:57	904663073	B8764	STOCK	0	0	B8764		03/A/001	4579917	17017	0		STOCK	0	0	B8764		02/A/001	4579917	17017	0		0	11/07/2020 03:57	FOPS6-UA/000		100	49579910		0	1	18.66	18.66	18.66		7.888	0		11/07/2020 03:07	kg	1.56	166118921			11/07/2020 03:00	StockMovement	-		1.00	18.66
25	11/07/2020 03:57	904663074	B8764	STOCK	0	0	B8764		03/A/001	4579917	17017	0		STOCK	0	0	B8764		02/A/001	4579917	17017	0		0	11/07/2020 03:57	FOPS6-UA/000		100	49579911		0	1	18.02	18.02	18.02		7.888	0		11/07/2020 03:07	kg	1.56	166118922			11/07/2020 03:00	StockMovement	-		1.00	18.02
26	11/07/2020 03:57	904663075	B8764	STOCK	0	0	B8764		03/A/001	4579917	17017	0		STOCK	0	0	B8764		02/A/001	4579917	17017	0		0	11/07/2020 03:57	FOPS6-UA/000		100	49579912		0	1	18.18	18.18	18.18		7.888	0		11/07/2020 03:07	kg	1.56	166118923			11/07/2020 03:00	StockMovement	-		1.00	18.18
27	11/07/2020 03:57	904663076	B8764	STOCK	0	0	B8764		03/A/001	4579917	17017	0		STOCK	0	0	B8764		02/A/001	4579917	17017	0		0	11/07/2020 03:57	FOPS6-UA/000		100	49579913		0	1	18.98	18.98	18.98		7.888	0		11/07/2020 03:07	kg	1.56	166118924			11/07/2020 03:00	StockMovement	-		1.00	18.98
28	11/07/2020 03:57	904663077	B8764	STOCK	0	0	B8764		03/A/001	4579917	17017	0		STOCK	0	0	B8764		02/A/001	4579917	17017	0		0	11/07/2020 03:57	FOPS6-UA/000		100	49579914		0	1	17.78	17.78	17.78		7.888	0		11/07/2020 03:07	kg	1.56	166118925			11/07/2020 03:00	StockMovement	-		1.00	17.78
IMV

odbcs140720.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W
1	pallet_number	stock_area	stock_rack	stock_location	modified_time	Total_boxes	Total_items	Total_box_label_weig	Max_life	Product	Description	Analysis_Code2	Location	Location2	Orig_Tx	Consumption Date
2	12625	3	A	1	1062719655	20	20	200	738747	S58	Veg 2 - 17780001	FROZEN	3/A/1	Behind Retail Cranes	04/06/2020 10:22	09/06/2020 10:22
3	16365	2	A	1	1062713701	35	35	952	738584	L8897	NZ LAMB FROZEN 75CL	FROZEN	2/A/1	A-B Link Tunnel	05/07/2020 03:47	10/07/2020 03:47		Lookup data
4	16467	2	A	1	1062712549	3	3	81.6	738594	8912	NZ 75CL TEMPERING	FROZEN	2/A/1	A-B Link Tunnel	06/07/2020 03:45	11/07/2020 03:45		Code	Description	Validation Type	IMV Column	Validation Days
5	16991	2	A	1	1062715912	13	13	246.31	738321	B8805	95VL OTM STEAK TRIM FROZEN	FROZEN	2/A/1	A-B Link Tunnel	10/07/2020 23:03	15/07/2020 23:03		S58	Veg 2 - 17780001	Veg Blends	A	1
6	17017	2	A	1	1062715917	5	5	92.98	1	B8764	95VL ROI STEAK TRIM FROZEN UTM	FROZEN	2/A/1	A-B Link Tunnel	11/07/2020 03:07	16/07/2020 03:07		S57	Veg 1 - 17760001	Veg Blends	A	1
7	17053	2	A	1	1062716874	20	20	368.54	738319	B8805	95VL OTM STEAK TRIM FROZEN	FROZEN	2/A/1	A-B Link Tunnel	11/07/2020 15:41	16/07/2020 15:41		8914	NZ LAMB SKIRTS TEMPERING	NZ Tempering	AN	5
8	17096	17	F	7	1062717611	35	35	673.34	1	B8374	98VL UTM IRISH TRIM FROZEN	FROZEN	17/F/7	UF Floor	11/07/2020 10:38	16/07/2020 10:38		B8805	95VL OTM STEAK TRIM FROZEN	Frozen Beef	AN	3
9	17097	17	F	7	1062717611	38	38	714.82	1	B8374	98VL UTM IRISH TRIM FROZEN	FROZEN	17/F/7	UF Floor	10/07/2020 22:03	15/07/2020 22:03		L8110	UK LAMB SKIRTS FROZEN	Frozen Lamb	AN	3
10	17099	17	F	7	1062717611	35	35	699.54	1	B8374	98VL UTM IRISH TRIM FROZEN	FROZEN	17/F/7	UF Floor	11/07/2020 02:01	16/07/2020 02:01		8912	NZ 75CL TEMPERING	NZ Tempering	AN	5
11	17101	17	F	7	1062717611	35	35	714.26	1	B8374	98VL UTM IRISH TRIM FROZEN	FROZEN	17/F/7	UF Floor	11/07/2020 02:00	16/07/2020 02:00		B8840	80VL OTM VALUE TRIM FROZEN	Frozen Beef	G	3
12	17105	17	F	7	1062717746	44	44	821.04	738004	B8373	98VL IRISH H/E STEAK TRIM		17/F/7	UF Floor	12/07/2020 08:13	17/07/2020 08:13		B8373	FINEST 95VL FROZEN	Frozen Beef	AN	3	#REF!
13	17107	17	F	7	1062717746	45	45	843.34	738004	B8373	98VL IRISH H/E STEAK TRIM		17/F/7	UF Floor	12/07/2020 08:24	17/07/2020 08:24		B8764	95VL ROI STEAK TRIM FROZEN UTM	Frozen Beef	AN	3	#REF!
14	17108	17	F	7	1062717746	45	45	842.08	738004	B8373	98VL IRISH H/E STEAK TRIM		17/F/7	UF Floor	12/07/2020 08:29	17/07/2020 08:29		B8374	98VL UTM IRISH TRIM FROZEN	Frozen Beef	AN	3
15	17109	17	F	7	1062717746	45	45	842.94	738004	L8810	UK LAMB SKIRTS FROZEN	FROZEN	17/F/7	UF Floor	12/07/2020 08:30	17/07/2020 08:30		L8806	ROI 80VL OTM TRIM FROZEN	Frozen Beef	AN	3
16	17110	17	F	7	1062717746	45	45	843.16	738004	B8373	98VL IRISH H/E STEAK TRIM		17/F/7	UF Floor	12/07/2020 08:35	17/07/2020 08:35
17	17112	17	F	7	1062717746	45	45	838.78	738004	B8373	98VL IRISH H/E STEAK TRIM		17/F/7	UF Floor	12/07/2020 08:40	17/07/2020 08:40
18	17113	17	F	7	1062717746	45	45	847.78	738004	B8373	98VL IRISH H/E STEAK TRIM		17/F/7	UF Floor	12/07/2020 08:42	17/07/2020 08:42			0
19	17183	3	A	1	1062719028	42	42	572.16	738009	L8110	NZ TB LEG		3/A/1	Behind Retail Cranes	13/07/2020 07:42	18/07/2020 07:42			16365
20	17185	3	A	1	1062719035	42	42	568.26	738010	L8110	NZ TB LEG		3/A/1	Behind Retail Cranes	13/07/2020 07:52	18/07/2020 07:52
21	17187	3	A	1	1062719053	19	19	361.38	738010	L8806	NZ LEAN LAMB LOINS 10 PER PACK		3/A/1	Behind Retail Cranes	13/07/2020 08:11	18/07/2020 08:11			AN
Union

Cell Formulas
Range	Formula
K2:K21	K2	=INDEX(Primal!C:C,MATCH([@Product],Primal!B:B,0))
L2:L21	L2	=INDEX(Primal!BT:BT,MATCH([@Product],Primal!B:B,0))
M2:M21	M2	=CONCATENATE([@[stock_area]],"/",[@[stock_rack]],"/",[@[stock_location]])
N2:N21	N2	=INDEX(Lookups!$B$2:$B$30,MATCH([@Location],Lookups!$A$2:$A$30,0))
O2:O21	O2	=INDEX(IMV!AN:AN,MATCH([@[pallet_number]],IMV!U:U,0))
P2:P21	P2	=[@[Orig_Tx]]+5
W12	W12	=INDEX(INDIRECT("IMV!"&ADDRESS(ROW(INDEX($U$4:$U$15,MATCH(Table_Query_from_FOPS6ODBC[@Product],$R$4:$R$15,0))),COLUMN(INDEX($U$4:$U$15,MATCH(Table_Query_from_FOPS6ODBC[@Product],$R$4:$R$15,0))),4)),MATCH(Table_Query_from_FOPS6ODBC[@[pallet_number]],IMV!U:U,0))
W13	W13	=INDEX(INDIRECT("IMV!"&ADDRESS(ROW(INDEX($U$4:$U$15,MATCH(Table_Query_from_FOPS6ODBC[@Product],$R$4:$R$15,0))),COLUMN(INDEX($U$4:$U$15,MATCH(Table_Query_from_FOPS6ODBC[@Product],$R$4:$R$15,0))),4)&":"&ADDRESS(ROW(INDEX($U$4:$U$15,MATCH(Table_Query_from_FOPS6ODBC[@Product],$R$4:$R$15,0))),COLUMN(INDEX($U$4:$U$15,MATCH(Table_Query_from_FOPS6ODBC[@Product],$R$4:$R$15,0))),4)),MATCH(Table_Query_from_FOPS6ODBC[@[pallet_number]],IMV!U:U,0))
S18	S18	=INDIRECT("IMV!"&ADDRESS(ROW(INDEX(Union!$T$4:$T$15,MATCH(Table_Query_from_FOPS6ODBC[@Product],Union!$R$4:$R$15,0))),COLUMN(INDEX(Union!$T$4:$T$15,MATCH(Table_Query_from_FOPS6ODBC[@Product],Union!$R$4:$R$15,0))),4)&":"&ADDRESS(ROW(INDEX(Union!$T$4:$T$15,MATCH(Table_Query_from_FOPS6ODBC[@Product],Union!$R$4:$R$15,0))),COLUMN(INDEX(Union!$T$4:$T$15,MATCH(Table_Query_from_FOPS6ODBC[@Product],Union!$R$4:$R$15,0))),4))
S19	S19	=INDIRECT("IMV!"&ADDRESS(ROW(INDEX($U$4:$U$15,MATCH(Table_Query_from_FOPS6ODBC[@Product],$R$4:$R$15,0))),COLUMN(INDEX($U$4:$U$15,MATCH(Table_Query_from_FOPS6ODBC[@Product],$R$4:$R$15,0))),4))
S21	S21	=INDIRECT(ADDRESS(ROW(INDEX($U$4:$U$15,MATCH(Table_Query_from_FOPS6ODBC[@Product],$R$4:$R$15,0))),COLUMN(INDEX($U$4:$U$15,MATCH(Table_Query_from_FOPS6ODBC[@Product],$R$4:$R$15,0))),4))

In the yellow formatted cells you can see what I've been attempting, however I cant get the indirect to parse correctly to return the right field from "IMV"

Cell S21 returns the "AN" from a lookup against the Union Table which is correct
Cell W31 is where I've tried to add it in to an index/indirect against IMV but it fails
Cell S19 is where I tried to debug it, and it's returning what seems to be cell U9 on IMV sheet, and 9 just happens to be the row that @product is found in the lookup table to the right & the column the lookup table is asked to check against.

This suggests to me it's the indirect syntax at the wrong point, influencing the lookups that proceed it but I'm really at a loss.

Thanks

 

[Toadstool]

Hi Purebluff,

There's still at least one missing sheet and without the named ranges I can't be sure how to recreate you sheet and execute any of the formulae, so let's just try a couple of debug steps.

Take the INDIRECT part off S19 and see what string the ADDRESS gives you.

What does the S18 return if you just take COLUMN part?
=COLUMN(INDEX(Union!$T$4:$T$15,MATCH(Table_Query_from_FOPS6ODBC[@Product],Union!$R$4:$R$15,0))),4)
ADDRESS just wants a column number so it may be easier to have another lookup data column so U with all the A, AN, G is followed by column V with 1,40,7.

 

[PureBluff]

Aha, my mistake, I forgot there was hidden sheets, I'll will post back with the results of your debugging suggestions within the hour & also include the additional sheets.
Apologies.

 

[Toadstool]

Please include the Named ranges in your XL2BB extract.

 

[PureBluff]

Ok,

IMV (This will be different to before as Im removing large parts of the transactional data / rows due to the 3000 cell limit of xl2bb)

odbcs140720.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC	AD	AE	AF	AG	AH	AI	AJ	AK	AL	AM	AN	AO	AP	AQ	AR	AS	AT	AU	AV	AW	AX	AY
1	Ins_timestamp	Id	Prod_code	From_status	From_ord_type	From_ord_no	From_ord_line	From_site	From_locn	From_pal_ser	From_pal_no	From_batch	From_doc	To_status	To_ord_type	To_ord_no	To_ord_line	To_site	To_locn	To_pal_ser	To_pal_no	To_batch	To_doc	Stk_adj_factor	Move_datetime	Terminal_port	Terminal_IP	Terminal_no	Trans_serial_no	User_code	Team_size	Curr_boxes	Curr_act_measure	Curr_label_kg	Curr_act_kg	Source	Ver_no	ExportStatus	Extra_detail	Trans_datetime	Unit_of_measure	Tare	Label_detail_id	Local_id	Local_site	Date	Type	Location	Site	items	Weight
2	04/06/2020 10:33	890106789	S58	PORDER	2	3008	S58			0	0	0		STOCK	0	0	S58		03/A/001	0	12625	0	GRN110575	1	04/06/2020 10:33	FOPS6-UC/000		97	439806		0	48	480	480	480		7.888	0		04/06/2020 10:22	kg	95.04	163407955			04/06/2020 10:00	AcceptedFromPO	3008		48	480
3	04/06/2020 11:10	890119469	S58	STOCK	0	0	S58		03/A/001	4544798	12625	0		STOCK	0	0	S58		08/A/004	4544798	12625	0		0	04/06/2020 11:10	FOPS6-UC/000		97	439806		0	48	480	480	480		7.888	0		04/06/2020 10:22	kg	95.04	163407955			04/06/2020 11:00	StockMovement	-		48	480
4	05/07/2020 03:49	902333190	L8897	STOCK	0	0	L8897		00/ /000	0	0	0		STOCK	0	0	L8897		08/A/015	4574416	16365	0		0	05/07/2020 03:49	FOPS6-UC/000		97	458129	JOZEFW	0	1	27.2	27.2	27.2		7.888	0		05/07/2020 03:47	kg	0.6	165683179			05/07/2020 03:00	StockMovement	-		1	27.2
5	05/07/2020 03:49	902333192	L8897	STOCK	0	0	L8897		00/ /000	0	0	0		STOCK	0	0	L8897		08/A/015	4574416	16365	0		0	05/07/2020 03:49	FOPS6-UC/000		97	458130	JOZEFW	0	1	27.2	27.2	27.2		7.888	0		05/07/2020 03:47	kg	0.6	165683180			05/07/2020 03:00	StockMovement	-		1	27.2
6	05/07/2020 03:52	902333398	L8897	STOCK	0	0	L8897		08/A/015	4574416	16365	0		STOCK	0	0	L8897		08/A/008	4574416	16365	0		0	05/07/2020 03:52	FOPS6-UC/000		97	458129	JOZEFW	0	1	27.2	27.2	27.2		7.888	0		05/07/2020 03:47	kg	0.6	165683179			05/07/2020 03:00	StockMovement	-		1	27.2
7	05/07/2020 03:52	902333399	L8897	STOCK	0	0	L8897		08/A/015	4574416	16365	0		STOCK	0	0	L8897		08/A/008	4574416	16365	0		0	05/07/2020 03:52	FOPS6-UC/000		97	458130	JOZEFW	0	1	27.2	27.2	27.2		7.888	0		05/07/2020 03:47	kg	0.6	165683180			05/07/2020 03:00	StockMovement	-		1	27.2
8	06/07/2020 21:46	902954750	S58	STOCK	0	0	S58		08/A/004	4544798	12625	0		STOCK	0	0	S58		08/A/002	4544798	12625	0		0	06/07/2020 21:46	FOPS6-UC/000		97	439806		0	48	480	480	480		7.888	0		04/06/2020 10:22	kg	95.04	163407955			06/07/2020 21:00	StockMovement	-		48	480
9	07/07/2020 01:27	903006492	L8897	STOCK	0	0	L8897		08/A/008	4574416	16365	0		STOCK	0	0	L8897		08/A/007	4574416	16365	0		0	07/07/2020 01:27	FOPS6-UC/000		97	458129	KASPARS	0	1	27.2	27.2	27.2		7.888	0		05/07/2020 03:47	kg	0.6	165683179			07/07/2020 01:00	StockMovement	-		1	27.2
10	07/07/2020 01:27	903006493	L8897	STOCK	0	0	L8897		08/A/008	4574416	16365	0		STOCK	0	0	L8897		08/A/007	4574416	16365	0		0	07/07/2020 01:27	FOPS6-UC/000		97	458130	KASPARS	0	1	27.2	27.2	27.2		7.888	0		05/07/2020 03:47	kg	0.6	165683180			07/07/2020 01:00	StockMovement	-		1	27.2
11	09/07/2020 15:01	904035931	L8897	STOCK	0	0	L8897		08/A/007	4574416	16365	0		STOCK	0	0	L8897		02/A/001	4574416	16365	0		0	09/07/2020 15:01	FOPS6-UC/000		97	458131		0	1	27.2	27.2	27.2		7.888	0		05/07/2020 03:47	kg	0.6	165683181			09/07/2020 15:00	StockMovement	-		1	27.2
12	09/07/2020 15:01	904035932	L8897	STOCK	0	0	L8897		08/A/007	4574416	16365	0		STOCK	0	0	L8897		02/A/001	4574416	16365	0		0	09/07/2020 15:01	FOPS6-UC/000		97	458132		0	1	27.2	27.2	27.2		7.888	0		05/07/2020 03:47	kg	0.6	165683182			09/07/2020 15:00	StockMovement	-		1	27.2
13	09/07/2020 15:01	904035963	L8897	STOCK	0	0	L8897		08/A/007	4574416	16365	0		STOCK	0	0	L8897		02/A/001	4574416	16365	0		0	09/07/2020 15:01	FOPS6-UC/000		97	458163		0	1	27.2	27.2	27.2		7.888	0		05/07/2020 03:47	kg	0.6	165683213			09/07/2020 15:00	StockMovement	-		1	27.2
14	10/07/2020 23:06	904580891	B8805	PORDER	2	149277	B8805			0	0	0		STOCK	0	0	B8805		06/A/001	0	16991	0	GRN111186	1	10/07/2020 23:06	FOPS6-UC/000		100	49577768		0	1	18.68	18.68	18.68	0512880524062031052119062000186889	7.888	0		10/07/2020 23:02	kg	1.58	166111013			10/07/2020 23:00	AcceptedFromPO	149277		1	18.68
15	10/07/2020 23:06	904580892	B8805	PORDER	2	149277	B8805			0	0	0		STOCK	0	0	B8805		06/A/001	0	16991	0	GRN111186	1	10/07/2020 23:06	FOPS6-UC/000		100	49577769		0	1	19.44	19.44	19.44	0512880524062031052119062000194489	7.888	0		10/07/2020 23:02	kg	1.58	166111014			10/07/2020 23:00	AcceptedFromPO	149277		1	19.44
16	10/07/2020 23:06	904580893	B8805	PORDER	2	149277	B8805			0	0	0		STOCK	0	0	B8805		06/A/001	0	16991	0	GRN111186	1	10/07/2020 23:06	FOPS6-UC/000		100	49577770		0	1	18.8	18.8	18.8	0512880524062031052119062000188089	7.888	0		10/07/2020 23:02	kg	1.58	166111015			10/07/2020 23:00	AcceptedFromPO	149277		1	18.8
17	10/07/2020 23:06	904580939	B8805	PORDER	2	149277	B8805			0	0	0		STOCK	0	0	B8805		06/A/001	0	16991	0	GRN111186	1	10/07/2020 23:06	FOPS6-UC/000		100	49577812		0	1	18.94	18.94	18.94	0512880524062031052119062000189489	7.888	0		10/07/2020 23:03	kg	1.58	166111058			10/07/2020 23:00	AcceptedFromPO	149277		1	18.94
18	10/07/2020 23:50	904595614	B8805	STOCK	0	0	B8805		06/A/001	4579752	16991	0		STOCK	0	0	B8805		03/A/001	4579752	16991	0		0	10/07/2020 23:50	FOPS6-UC/000		100	49577768		0	1	18.68	18.68	18.68		7.888	0		10/07/2020 23:02	kg	1.58	166111013			10/07/2020 23:00	StockMovement	-		1	18.68
19	10/07/2020 23:50	904595615	B8805	STOCK	0	0	B8805		06/A/001	4579752	16991	0		STOCK	0	0	B8805		03/A/001	4579752	16991	0		0	10/07/2020 23:50	FOPS6-UC/000		100	49577769		0	1	19.44	19.44	19.44		7.888	0		10/07/2020 23:02	kg	1.58	166111014			10/07/2020 23:00	StockMovement	-		1	19.44
20	10/07/2020 23:50	904595616	B8805	STOCK	0	0	B8805		06/A/001	4579752	16991	0		STOCK	0	0	B8805		03/A/001	4579752	16991	0		0	10/07/2020 23:50	FOPS6-UC/000		100	49577770		0	1	18.8	18.8	18.8		7.888	0		10/07/2020 23:02	kg	1.58	166111015			10/07/2020 23:00	StockMovement	-		1	18.8
21	11/07/2020 03:10	904647848	B8764	PORDER	2	149270	B8764			0	0	0		STOCK	0	0	B8764		06/A/001	0	17017	0	GRN111200	1	11/07/2020 03:10	FOPS6-UC/000		100	49579910		0	1	18.66	18.66	18.66	0552876425052025052121052000186660	7.888	0		11/07/2020 03:07	kg	1.56	166118921			11/07/2020 03:00	AcceptedFromPO	149270		1	18.66
22	11/07/2020 03:10	904647849	B8764	PORDER	2	149270	B8764			0	0	0		STOCK	0	0	B8764		06/A/001	0	17017	0	GRN111200	1	11/07/2020 03:10	FOPS6-UC/000		100	49579911		0	1	18.02	18.02	18.02	0552876425052025052121052000180260	7.888	0		11/07/2020 03:07	kg	1.56	166118922			11/07/2020 03:00	AcceptedFromPO	149270		1	18.02
23	11/07/2020 03:10	904647850	B8764	PORDER	2	149270	B8764			0	0	0		STOCK	0	0	B8764		06/A/001	0	17017	0	GRN111200	1	11/07/2020 03:10	FOPS6-UC/000		100	49579912		0	1	18.18	18.18	18.18	0552876421052021052118052000181860	7.888	0		11/07/2020 03:07	kg	1.56	166118923			11/07/2020 03:00	AcceptedFromPO	149270		1	18.18
24	11/07/2020 03:36	904657104	B8764	STOCK	0	0	B8764		06/A/001	4579917	17017	0		STOCK	0	0	B8764		03/A/001	4579917	17017	0		0	11/07/2020 03:36	FOPS6/000		100	49579935		0	1	18.24	18.24	18.24		7.888	0		11/07/2020 03:08	kg	1.56	166118947			11/07/2020 03:00	StockMovement	-		1	18.24
25	11/07/2020 03:52	904661986	B8805	STOCK	0	0	B8805		03/A/001	4579752	16991	0		STOCK	0	0	B8805		02/A/001	4579752	16991	0		0	11/07/2020 03:52	FOPS6-UC/000		100	49577770	JOZEFW	0	1	18.8	18.8	18.8		7.888	0		10/07/2020 23:02	kg	1.58	166111015			11/07/2020 03:00	StockMovement	-		1	18.8
26	11/07/2020 03:52	904661987	B8805	STOCK	0	0	B8805		03/A/001	4579752	16991	0		STOCK	0	0	B8805		02/A/001	4579752	16991	0		0	11/07/2020 03:52	FOPS6-UC/000		100	49577771	JOZEFW	0	1	18.49	18.49	18.49		7.888	0		10/07/2020 23:02	kg	1.58	166111016			11/07/2020 03:00	StockMovement	-		1	18.49
27	11/07/2020 03:52	904661988	B8805	STOCK	0	0	B8805		03/A/001	4579752	16991	0		STOCK	0	0	B8805		02/A/001	4579752	16991	0		0	11/07/2020 03:52	FOPS6-UC/000		100	49577772	JOZEFW	0	1	18.56	18.56	18.56		7.888	0		10/07/2020 23:02	kg	1.58	166111017			11/07/2020 03:00	StockMovement	-		1	18.56
28	11/07/2020 03:52	904661989	B8805	STOCK	0	0	B8805		03/A/001	4579752	16991	0		STOCK	0	0	B8805		02/A/001	4579752	16991	0		0	11/07/2020 03:52	FOPS6-UC/000		100	49577773	JOZEFW	0	1	18.62	18.62	18.62		7.888	0		10/07/2020 23:02	kg	1.58	166111018			11/07/2020 03:00	StockMovement	-		1	18.62
29	11/07/2020 03:52	904661990	B8805	STOCK	0	0	B8805		03/A/001	4579752	16991	0		STOCK	0	0	B8805		02/A/001	4579752	16991	0		0	11/07/2020 03:52	FOPS6-UC/000		100	49577774	JOZEFW	0	1	18.44	18.44	18.44		7.888	0		10/07/2020 23:02	kg	1.58	166111019			11/07/2020 03:00	StockMovement	-		1	18.44
30	11/07/2020 03:52	904661991	B8805	STOCK	0	0	B8805		03/A/001	4579752	16991	0		STOCK	0	0	B8805		02/A/001	4579752	16991	0		0	11/07/2020 03:52	FOPS6-UC/000		100	49577775	JOZEFW	0	1	18.72	18.72	18.72		7.888	0		10/07/2020 23:02	kg	1.58	166111020			11/07/2020 03:00	StockMovement	-		1	18.72
IMV

Union (changes for debugging in Orange cells) - S18 needed a larger syntax change than suggested

odbcs140720.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W
1	pallet_number	stock_area	stock_rack	stock_location	modified_time	Total_boxes	Total_items	Total_box_label_weig	Max_life	Product	Description	Analysis_Code2	Location	Location2	Orig_Tx	Consumption Date
2	12625	3	A	1	1062719655	20	20	200	738747	S58	Veg 2 - 17780001	FROZEN	3/A/1	Behind Retail Cranes	04/06/2020 10:22	09/06/2020 10:22
3	16365	2	A	1	1062713701	35	35	952	738584	L8897	NZ LAMB FROZEN 75CL	FROZEN	2/A/1	A-B Link Tunnel	05/07/2020 03:47	10/07/2020 03:47		Code	Description	Validation Type	IMV Column	Validation Days
4	16991	2	A	1	1062715912	13	13	246.31	738321	B8805	95VL OTM STEAK TRIM FROZEN	FROZEN	2/A/1	A-B Link Tunnel	10/07/2020 23:02	15/07/2020 23:02		L8806	Veg 2 - 17780001	Veg Blends	A	1
5	17017	2	A	1	1062715917	5	5	92.98	1	B8764	95VL ROI STEAK TRIM FROZEN UTM	FROZEN	2/A/1	A-B Link Tunnel	11/07/2020 03:07	16/07/2020 03:07		S57	Veg 1 - 17760001	Veg Blends	A	1
6	17053	2	A	1	1062716874	20	20	368.54	738319	B8805	95VL OTM STEAK TRIM FROZEN	FROZEN	2/A/1	A-B Link Tunnel	11/07/2020 15:41	16/07/2020 15:41		8914	NZ LAMB SKIRTS TEMPERING TWA	NZ Tempering	AN	5
7	17096	17	F	7	1062717611	35	35	673.34	1	B8374	98VL UTM IRISH TRIM FROZEN	FROZEN	17/F/7	UF Floor	11/07/2020 10:38	16/07/2020 10:38		B8805	95VL OTM STEAK TRIM FROZEN	Frozen Beef	AN	3
8	17097	17	F	7	1062717611	38	38	714.82	1	B8374	98VL UTM IRISH TRIM FROZEN	FROZEN	17/F/7	UF Floor	10/07/2020 22:03	15/07/2020 22:03		L8110	UK LAMB SKIRTS FROZEN	Frozen Lamb	AN	3
9	17099	17	F	7	1062717611	35	35	699.54	1	B8374	98VL UTM IRISH TRIM FROZEN	FROZEN	17/F/7	UF Floor	11/07/2020 02:01	16/07/2020 02:01		8912	NZ 75CL TEMPERING TWA	NZ Tempering	AN	5
10	17101	17	F	7	1062717611	35	35	714.26	1	B8374	98VL UTM IRISH TRIM FROZEN	FROZEN	17/F/7	UF Floor	11/07/2020 02:00	16/07/2020 02:00		B8840	80VL OTM VALUE TRIM FROZEN	Frozen Beef	G	3
11	17105	17	F	7	1062717746	44	44	821.04	738004	B8373	98VL IRISH H/E STEAK TRIM		17/F/7	UF Floor	12/07/2020 08:13	17/07/2020 08:13		B8373	FINEST 95VL FROZEN	Frozen Beef	AN	3
12	17107	17	F	7	1062717746	45	45	843.34	738004	B8373	98VL IRISH H/E STEAK TRIM		17/F/7	UF Floor	12/07/2020 08:24	17/07/2020 08:24		B8764	95VL ROI STEAK TRIM FROZEN UTM	Frozen Beef	AN	3	#REF!
13	17108	17	F	7	1062717746	45	45	842.08	738004	B8373	98VL IRISH H/E STEAK TRIM		17/F/7	UF Floor	12/07/2020 08:29	17/07/2020 08:29		B8374	98VL UTM IRISH TRIM FROZEN	Frozen Beef	AN	3	#REF!
14	17109	17	F	7	1062717746	45	45	842.94	738004	B8373	98VL IRISH H/E STEAK TRIM		17/F/7	UF Floor	12/07/2020 08:30	17/07/2020 08:30		B8767	ROI 80VL OTM TRIM FROZEN	Frozen Beef	AN	3
15	17110	17	F	7	1062717746	45	45	843.16	738004	B8373	98VL IRISH H/E STEAK TRIM		17/F/7	UF Floor	12/07/2020 08:35	17/07/2020 08:35
16	17112	17	F	7	1062717746	45	45	838.78	738004	B8373	98VL IRISH H/E STEAK TRIM		17/F/7	UF Floor	12/07/2020 08:40	17/07/2020 08:40
17	17113	17	F	7	1062717746	45	45	847.78	738004	B8373	98VL IRISH H/E STEAK TRIM		17/F/7	UF Floor	12/07/2020 08:42	17/07/2020 08:42
18	17187	3	A	1	1062719053	19	19	361.38	738010	L8806	NZ LEAN LAMB LOINS 10 PER PACK		3/A/1	Behind Retail Cranes	13/07/2020 08:11	18/07/2020 08:11			20
19	17194	3	A	1	1062719142	40	40	574.98	738010	L8110	NZ TB LEG		3/A/1	Behind Retail Cranes	13/07/2020 09:35	18/07/2020 09:35			U8
20	17213	2	A	1	1062719784	3	3	55.6	738358	B8767	ROI 80VL OTM TRIM FROZEN	FROZEN	2/A/1	A-B Link Tunnel	13/07/2020 17:38	18/07/2020 17:38
21	17214	2	A	1	1062719784	33	33	612.7	738358	B8767	ROI 80VL OTM TRIM FROZEN	FROZEN	2/A/1	A-B Link Tunnel	13/07/2020 17:46	18/07/2020 17:46			AN
Union

Cell Formulas
Range	Formula
K2:K21	K2	=INDEX(Primal!C:C,MATCH([@Product],Primal!B:B,0))
L2:L21	L2	=INDEX(Primal!BT:BT,MATCH([@Product],Primal!B:B,0))
M2:M21	M2	=CONCATENATE([@[stock_area]],"/",[@[stock_rack]],"/",[@[stock_location]])
N2:N21	N2	=INDEX(Lookups!$B$2:$B$30,MATCH([@Location],Lookups!$A$2:$A$30,0))
O2:O21	O2	=INDEX(IMV!AN:AN,MATCH([@[pallet_number]],IMV!U:U,0))
P2:P21	P2	=[@[Orig_Tx]]+5
W12	W12	=INDEX(INDIRECT("IMV!"&ADDRESS(ROW(INDEX($U$4:$U$15,MATCH(Table_Query_from_FOPS6ODBC[@Product],$R$4:$R$15,0))),COLUMN(INDEX($U$4:$U$15,MATCH(Table_Query_from_FOPS6ODBC[@Product],$R$4:$R$15,0))),4)),MATCH(Table_Query_from_FOPS6ODBC[@[pallet_number]],IMV!U:U,0))
W13	W13	=INDEX(INDIRECT("IMV!"&ADDRESS(ROW(INDEX($U$4:$U$15,MATCH(Table_Query_from_FOPS6ODBC[@Product],$R$4:$R$15,0))),COLUMN(INDEX($U$4:$U$15,MATCH(Table_Query_from_FOPS6ODBC[@Product],$R$4:$R$15,0))),4)&":"&ADDRESS(ROW(INDEX($U$4:$U$15,MATCH(Table_Query_from_FOPS6ODBC[@Product],$R$4:$R$15,0))),COLUMN(INDEX($U$4:$U$15,MATCH(Table_Query_from_FOPS6ODBC[@Product],$R$4:$R$15,0))),4)),MATCH(Table_Query_from_FOPS6ODBC[@[pallet_number]],IMV!U:U,0))
S18	S18	=COLUMN(INDEX(Union!$T$4:$T$15,MATCH(Table_Query_from_FOPS6ODBC[@Product],Union!$R$4:$R$15,0)))
S19	S19	=ADDRESS(ROW(INDEX($U$4:$U$15,MATCH(Table_Query_from_FOPS6ODBC[@Product],$R$4:$R$15,0))),COLUMN(INDEX($U$4:$U$15,MATCH(Table_Query_from_FOPS6ODBC[@Product],$R$4:$R$15,0))),4)
S21	S21	=INDIRECT(ADDRESS(ROW(INDEX($U$4:$U$15,MATCH(Table_Query_from_FOPS6ODBC[@Product],$R$4:$R$15,0))),COLUMN(INDEX($U$4:$U$15,MATCH(Table_Query_from_FOPS6ODBC[@Product],$R$4:$R$15,0))),4))

Lookups (redundant for now, but I'll be moving Union!S:V there in the future)

odbcs140720.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M
1	Fops Locations	Physical		Code	Description	Validation Type	IMV Column	Validation Days			Validation Type	Validation Days	IMV Column
2	1/A/1	Dawson		S58	Veg 2 - 17780001	Veg Blends	A	1			Frozen Beef	3	AN
3	2/A/1	A-B Link Tunnel		S57	Veg 1 - 17760001	Veg Blends	A	1			Frozen Lamb	3	AN
4	3/A/1	Behind Retail Cranes		8914	NZ LAMB SKIRTS TEMPERING TWA	NZ Tempering	AN	5			NZ Tempering	5	AN
5	4/A/1	UA Warehouse Floor		B8805	95VL OTM STEAK TRIM FROZEN	Frozen Beef	AN	3			Ingredients	1	A
6	5/A/1	UA Racking Floor		L8810	UK LAMB SKIRTS FROZEN	Frozen Lamb	AN	3			Meat Free	1	A
7	6/A/1	UC Warehouse Floor		8912	NZ 75CL TEMPERING TWA	NZ Tempering	AN	5			Veg Blends	1	A
8	8/A/1	Ingredients Trailer		B8840	80VL OTM VALUE TRIM FROZEN	Frozen Beef	AN	3
9	8/A/2	Frozen Trailer		B8874	FINEST 95VL FROZEN	Frozen Beef	AN	3
10	8/A/3	Frozen Trailer		B8764	95VL ROI STEAK TRIM FROZEN UTM	Frozen Beef	AN	3
11	8/A/4	Frozen Trailer		B8374	98VL UTM IRISH TRIM FROZEN	Frozen Beef	AN	3
12	8/A/5	Stock Trailer		B8767	ROI 80VL OTM TRIM FROZEN	Frozen Beef	AN	3
Lookups

Cell Formulas
Range	Formula
G2:G12	G2	=IF(D2="","",INDEX($M$2:$M$35,MATCH(F2,$K$2:$K$35,0)))
H2:H12	H2	=IF(D2="","",INDEX($L$2:$L$35,MATCH(F2,$K$2:$K$35,0)))
E2:E12	E2	=INDEX(Primal!C:C,MATCH(D2,Primal!B:B,0))

 

[PureBluff]

and finally the primal worksheet (Again, cleansed data due to the size of the table)

odbcs140720.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC	AD	AE	AF	AG	AH	AI	AJ	AK	AL	AM	AN	AO	AP	AQ	AR	AS	AT	AU	AV	AW	AX	AY	AZ	BA	BB	BC	BD	BE	BF	BG	BH	BI	BJ	BK	BL	BM	BN	BO	BP	BQ	BR	BS	BT	BU	BV	BW	BX	BY	BZ	CA	CB	CC	CD	CE	CF	CG	CH	CI	CJ	CK	CL	CM	CN	CO	CP	CQ	CR	CS	CT	CU	CV	CW	CX	CY	CZ	DA	DB	DC	DD	DE	DF	DG	DH	DI	DJ	DK	DL	DM	DN	DO	DP	DQ	DR	DS	DT	DU	DV	DW	DX	DY	DZ	EA	EB	EC	ED	EE	EF	EG	EH	EI	EJ	EK	EL	EM	EN	EO	EP	EQ	ER	ES	ET	EU	EV	EW	EX	EY	EZ	FA	FB	FC	FD	FE	FF	FG	FH	FI	FJ	FK	FL
1	Product_code	WIP	Description	Reserved5	Dcp_code	Site_code	Old_soc	Dump1	Dump2	Dump3	Dump4	Dump5	Dump6	Dump7	Dump8	Dump9	Dump10	Dump11	Dump12	Dump13	Dump14	Dump15	Dump16	OldLabFormat	Oldbcode_lookup	Prstkrmfrom	Weight_range	Text_1	Text_2	Text_3	Text_4	Text_5	Text_6	Text_7	Text_8	Tare	High_tolerance	Low_tolerance	Date_type_1	Date_advance_1	Date_from_1	Date_type_2	Date_advance_2	Date_from_2	Date_type_3	Date_advance_3	Date_from_3	Date_type_4	Date_advance_4	Date_from_4	Barcode_date	Transaction_date_ref	Number_of_labels	Tolerance_on	Price	Standard_weight	OldNPP	Printer	Fixed_weight	Assign_as	Batch_out_suffix	Link_code	Auto_group_code	Pack_code	Packs_per_case	Standard_yield	Inners	Fixed_barcode	Product_eq	Batch_group	Analysis_code_1	Analysis_code_2	Analysis_code_3	Analysis_code_4	Packs_per_InternTray	Parent	Slicing_line	Chiller	Default_order_units	Reserved3	Reserved4	Min_prod_date	Max_prod_date	Sales_order_code	Min_prod_time	Max_prod_time	Nominal_code	Abbrv_description	Batch_in_suffix	Barcode_lookup	Barcode_offset	Delivery_export	Team_Size	Imported	Product_yield	Pcsppack	Prstkrmto	Issstkrmfrom	Issstkrmto	Prtupoondel	Pick_area	Pick_rack	Pick_slot	DTP_area	DTP_rack	DTP_slot	Comp_group_plu	Wt_Adjust	JS_SKU	AddSapItemPfx	CoGrp_SOP_Code	CoGrp_Supp_Id	Import_substitute	Trace_codes_Id	Second_nominal	Barcode_Format	Alt_Desp_Locn	Alt_Pick_Area	Alt_Pick_Rack	Alt_Pick_Loc	ExpBatAs1Item	ReOrdStockLevel	ReOrdQty	ReOrdUnits	Boxes_per_pallet	Dump_selection	Lean_percent	kill_pack_max_diff	Alt_stk_analysis	Exclude_from_ocm	Transponder_reqd	OCM_bit3	OCM_bit4	OCM_bit5	OCM_bit6	OCM_bit7	OCM_bit8	IntrastatCode	RetainBatchOnPc	RetainBatchUntilRc	RetainBatchUntilSPRC	RBOPC_bit4	RBOPC_bit5	RBOPC_bit6	RBOPC_bit7	RBOPC_bit8	Exclude_from_FO	Exclude_from_stock	Exclude_bit3	Exclude_bit4	Exclude_bit5	Exclude_bit6	Exclude_bit7	Exclude_bit8	Process_unit	ProcessUnitsPerCase	Std_net_kg	Process_base_wt	Process_base_wt_gain	Proc_gain_tol_pcent	Pallet_type	Customer_code	Equipment2_code	ANA_on_pallet	Pack_life_1	Pack_life_2	Label_format	Plu_number
2	B8373	B8373	98VL IRISH H/E STEAK TRIM	8373				FALSE	FALSE	TRUE	FALSE	TRUE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	N			K	1746	448	0	1739	0	0	0	0	1.66	23	10	1	0	P	D	28	1	J	0	1	J	0	1	1	1	1	N	0	20	60	1	0	0	812				0	0	0	00008373000000	0	A	PRIMAL			GREEN3	0		00			3	1	0	0		0	0			812	8373	0304	FALSE	0	FALSE	0	0				FALSE	0		0	0		0	0	0		FALSE	B8373		FALSE	0		0		0		0	FALSE	0	0	K	60	20	95	0		FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE		TRUE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE		0	20	6.0135E-154	6.0135E-154	6.0135E-154					3	1	N	8373
3	B8764	B8764	95VL ROI STEAK TRIM FROZEN UTM	8764				FALSE	FALSE	FALSE	FALSE	TRUE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	N			K	1746	1801	320	1739	0	0	0	0	1.68	23	15	K	0	P	W	24	1	A	0	2	J	0	1	1	1	1	N	0	20	60	1	0	0	809				0	0	0	00008764000000	0	A	PRIMAL	FROZEN		GREEN3	0		00	0		5	0	0	0		0	0			809	8764	0304	FALSE	0	FALSE	0	0				FALSE	0		0	0		0	0	0		FALSE	B8764		FALSE	0		0		0		0	FALSE	0	0	K	60	16	88	0		FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE		TRUE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE		0	20	6.0135E-154	6.0135E-154	6.0135E-154					0	0	N	8764
4	B8805	B8805	95VL OTM STEAK TRIM FROZEN	8805				FALSE	FALSE	FALSE	FALSE	TRUE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	N			K	1746	1801	8937	1739	0	0	0	0	1.68	25	15	K	0	P	W	12	1	A	0	1	J	0	1	1	1	1	N	0	20	60	1	0	0	809				0	0	0	00008805000000	0	A	PRIMAL	FROZEN		GREEN3	0		00	0		5	0	0	0		0	0			809	8805	0304	FALSE	0	FALSE	0	0				FALSE	0		0	0		0	8805	0		FALSE	B8805		FALSE	0		0		0		0	FALSE	0	0	K	60	16	88	0		FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE		TRUE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE		0	20	6.0135E-154	6.0135E-154	6.0135E-154					0	0	N	8805
5	L8806	L8806	NZ LEAN LAMB LOINS 10 PER PACK	8806				FALSE	FALSE	FALSE	FALSE	TRUE	TRUE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	N			K	1746	0	2402	1739	0	0	0	0	2.14	32	2	L	0	P	A	0	1	1	0	1	D	65	1	3	11	1	N	0	15	60	1	0	0	801				6	96	0	00008806000000	0	A	PRIMAL6			GREEN3	0		00	0		5	0	0	0		0	0			801	8806	0304	FALSE	0	FALSE	0	0				FALSE	0		0	0		0	8806	0		FALSE	L8806		FALSE	0		0		0		0	FALSE	0	0	K	60	48	0	0		FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE		FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE		0	15	6.0135E-154	6.0135E-154	6.0135E-154					5	1	N	8806
6	L8810	L8810	UK LAMB SKIRTS FROZEN	8810				FALSE	FALSE	FALSE	FALSE	TRUE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	N			K	1746	229	10	1739	0	0	0	0	0.6	32	2	K	0	P	W	24	1	A	0	1	J	0	1	2	1	1	N	0	27.2	60	1	0	0	832				0	0	0	00008810000000	0	A	PRIMAL	FROZEN		GREEN3	0		00			0	0	0	0		0	0			832	8810	0304	FALSE	0	FALSE	0	0				FALSE	0		0	0		0	0	0		FALSE	L8810		FALSE	0		0		0		0	FALSE	0	0	K	60	16	0	0		FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE		FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE		0	27.2	6.0135E-154	6.0135E-154	6.0135E-154					0	0	N	8810
7	B8874	B8874	FINEST 95VL FROZEN	8874				FALSE	FALSE	FALSE	FALSE	TRUE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	N			K	1746	1801	0	1739	0	0	0	0	1.68	25	18	K	0	P	W	12	1	A	0	1	J	0	1	1	1	1	N	0	20	60	1	0	0	809				0	0	0	00008874000000	0	A	PRIMAL	FROZEN		GREEN3	0		00	0		0	0	0	0		0	0			809	8874	0304	FALSE	0	FALSE	0	0				FALSE	0		0	0		0	0	0		FALSE	B8874		FALSE	0		0		0		0	FALSE	0	0	K	60	16	88	0		FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE		TRUE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE		0	20	6.0135E-154	6.0135E-154	6.0135E-154					0	0	N	8874
8	B8873	B8873	FINEST 85VL FRESH	8873				FALSE	FALSE	FALSE	FALSE	TRUE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	N			K	1746	163	0	1739	0	0	0	0	1.7	23	15	A	0	P	D	30	1	J	0	1	J	0	1	1	1	1	N	0	20	60	1	0	0	812				0	0	0	00008873000000	0	A	PRIMAL			GREEN3	0		00			3	1	0	0		0	0			812	8873	0304	TRUE	0	FALSE	0	0				FALSE	0		0	0		0	0	0		FALSE	B8873		FALSE	0		0		0		0	FALSE	0	0	K	60	16	80	0		FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE		TRUE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE		0	20	6.0135E-154	6.0135E-154	6.0135E-154					3	1	N	8873
Primal

Cell Formulas
Range	Formula
B2:B8	B2	=TRIM([@[Product_code]])

Thanks for your help on this toadstool, I really do appreciate it.

 

[PureBluff]

Please note, there are no named ranges in this, only 3 tables: