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

PureBluff

Board Regular
Joined
Apr 4, 2014
Messages
174
Office Version
  1. 2016
Platform
  1. Windows
  2. Mobile
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
DEFGH
1CodeDescriptionValidation TypeTimestamp ColumnValidation Days
2S58Veg 2 - 17780001 Veg BlendsA1
3S57Veg 1 - 17760001 Veg BlendsA1
48914NZ LAMB SKIRTS TEMPERING NZ TemperingAN5
5B880595VL OTM STEAK TRIM FROZEN Frozen CratedAN3
6L8810UK LAMB SKIRTS FROZEN Frozen CratedAN3
78912NZ 75CL TEMPERING NZ TemperingAN5
8B884080VL OTM VALUE TRIM FROZEN Frozen CratedAN3
9B887495VL FROZEN Frozen CratedAN3
10B876495VL ROI STEAK TRIM FROZEN UTM Frozen CratedAN3
11B837498VL UTM IRISH TRIM FROZEN Frozen CratedAN3
12B8767ROI 80VL OTM TRIM FROZEN Frozen CratedAN3
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
ABCDEFGHIJKLMNOPQRS
1pallet_numberstock_areastock_rackstock_locationmodified_timeTotal_boxesTotal_itemsTotal_box_label_weigMax_lifeProductDescriptionAnalysis_Code2LocationLocation2Orig_TxConsumption Date
2126272A110627110151212120738747S58Veg 2 - 17780001 FROZEN 2/A/1A-B Link Tunnel04/06/2020 10:2407/06/2020 10:2404/6/2020 10:33
3161892A110627118083360.87738229L8810UK LAMB SKIRTS FROZEN FROZEN 2/A/1A-B Link Tunnel22/04/2020 12:0425/04/2020 12:04
4162122A11062706670551367385948912NZ 75CL TEMPERING FROZEN 2/A/1A-B Link Tunnel03/07/2020 15:3506/07/2020 15:35
Union


I really hope that this makes some sense!
 
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.
1594761202993.png


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
RSTUVW
3CodeDescriptionValidation TypeIMV ColumnColumn No.Validation Days
4L8806Veg 2 - 17780001 Veg BlendsA11
5S57Veg 1 - 17760001 Veg BlendsA11
68914NZ LAMB SKIRTS TEMPERING TWA NZ TemperingAN405
7B880595VL OTM STEAK TRIM FROZEN Frozen BeefAN403
8L8110UK LAMB SKIRTS FROZEN Frozen LambAN403
98912NZ 75CL TEMPERING TWA NZ TemperingAN405
10B884080VL OTM VALUE TRIM FROZEN Frozen BeefG73
11B8373FINEST 95VL FROZEN Frozen BeefAN403
12B876495VL ROI STEAK TRIM FROZEN UTM Frozen BeefAN403
13B837498VL UTM IRISH TRIM FROZEN Frozen BeefAN403
14B8767ROI 80VL OTM TRIM FROZEN Frozen BeefAN403
15
16
17
1820
1912625IMV!U8AN4/6/2020 10:22
Union
Cell Formulas
RangeFormula
V4:V14V4=COLUMN(INDIRECT((U4)&":"&(U4)))
S18S18=COLUMN(INDEX(Union!$T$4:$T$15,MATCH(Table_Query_from_FOPS6ODBC[@Product],Union!$R$4:$R$15,0)))
S19S19=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))
T19T19="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)
U19U19=INDEX($U$4:$U$15,MATCH(Table_Query_from_FOPS6ODBC[@Product],$R$4:$R$15,0))
V19V19=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))
 
Upvote 0

Excel Facts

VLOOKUP to Left?
Use =VLOOKUP(A2,CHOOSE({1,2},$Z$1:$Z$99,$Y$1:$Y$99),2,False) to lookup Y values to left of Z values.

Forum statistics

Threads
1,213,534
Messages
6,114,185
Members
448,554
Latest member
Gleisner2

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
Back
Top