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]

I changed a Table name to match your formula so an image of your Formulas, Name Manager would help if you make any further changes.

Continuing to work on your debug S19, if I just remove the INDIRECT so it's
="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)
...then it gives me IMV!U8 so an INDIRECT to IMV cell U8 is indeed 12625.

Are you expecting the contents of AN8?
The part of that formula COLUMN(INDEX($U$4:$U$15,MATCH(Table_Query_from_FOPS6ODBC[@Product],$R$4:$R$15,0)))
returns 21 because U is the 21st column. It's not taking the contents AN but the column specified, 21.

The easiest solution is to insert a new column V for the column number and populate it with =COLUMN(INDIRECT((U4)&":"&(U4)))

Now your formula is simplified, as shown in debug cell V19, to
=INDIRECT("IMV!"&ADDRESS(ROW(INDEX($U$4:$U$15,MATCH(Table_Query_from_FOPS6ODBC[@Product],$R$4:$R$15,0))),INDEX($V$4:$V$15,MATCH(Table_Query_from_FOPS6ODBC[@Product],$R$4:$R$15,0)),4))

PureBluff3.xlsx
	R	S	T	U	V	W
3	Code	Description	Validation Type	IMV Column	Column No.	Validation Days
4	L8806	Veg 2 - 17780001	Veg Blends	A	1	1
5	S57	Veg 1 - 17760001	Veg Blends	A	1	1
6	8914	NZ LAMB SKIRTS TEMPERING TWA	NZ Tempering	AN	40	5
7	B8805	95VL OTM STEAK TRIM FROZEN	Frozen Beef	AN	40	3
8	L8110	UK LAMB SKIRTS FROZEN	Frozen Lamb	AN	40	3
9	8912	NZ 75CL TEMPERING TWA	NZ Tempering	AN	40	5
10	B8840	80VL OTM VALUE TRIM FROZEN	Frozen Beef	G	7	3
11	B8373	FINEST 95VL FROZEN	Frozen Beef	AN	40	3
12	B8764	95VL ROI STEAK TRIM FROZEN UTM	Frozen Beef	AN	40	3
13	B8374	98VL UTM IRISH TRIM FROZEN	Frozen Beef	AN	40	3
14	B8767	ROI 80VL OTM TRIM FROZEN	Frozen Beef	AN	40	3
15
16
17
18		20
19		12625	IMV!U8	AN	4/6/2020 10:22
Union

Cell Formulas
Range	Formula
V4:V14	V4	=COLUMN(INDIRECT((U4)&":"&(U4)))
S18	S18	=COLUMN(INDEX(Union!$T$4:$T$15,MATCH(Table_Query_from_FOPS6ODBC[@Product],Union!$R$4:$R$15,0)))
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))
T19	T19	="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)
U19	U19	=INDEX($U$4:$U$15,MATCH(Table_Query_from_FOPS6ODBC[@Product],$R$4:$R$15,0))
V19	V19	=INDIRECT("IMV!"&ADDRESS(ROW(INDEX($U$4:$U$15,MATCH(Table_Query_from_FOPS6ODBC[@Product],$R$4:$R$15,0))),INDEX($V$4:$V$15,MATCH(Table_Query_from_FOPS6ODBC[@Product],$R$4:$R$15,0)),4))