I am creating a continuous data set of materials and their inventory in a certain week.
I want to be able to lookup what the inventory of a specific material was in a certain week. Through writing the lookup week and lookup material name.
I am now using a Macro to copy the values of a query and inserting new columns, which means the data will be pushed to the right. The only issue is that I cannot identify the correct row number for the lookup, as multiple rows can consist of the same material number.
If anybody knows if this issue can be solved or if there is another sustainable way of using another method to record and lookup the correct data, than I am all ears for it!
I'm able to identify is the correct column number using the match function: =MATCH(C4;A5:J5;0)
From which I came up with a system to determine the lookup array using the column number. See the image.
The issue is however that Excel does not seem to allow the lookup array to be determined by another formula, value or cell.
There's a few ways to do this.
You have the dynamic array tag, so there are features in O365 Excel that make this easier. One is to FILTER() the columns using C4, and then do a VLOOKUP() in that filtered sub-table using C5:
=VLOOKUP( C5 , FILTER( E6:J8 , E5:J5 = C4 ) , 2)
If you do not have Excel O365:
If you do not have Excel O365, then life might be made easier by "normalizing" the column headers so that each individual column can be referenced uniquely. So you'd have "Week 3 Type" and "Week 3 Qty" columns that you can access individually.
Also, and this could be a big deal if you don't have O365, unique column names enables the dataset table to potentially be an actual excel-defined table data structure. Because it's a volatile function, you have to be careful about using large numbers of INDIRECT() statements in a large spreadsheet. But in this case you'd only be using TWO of them so the performance impact should be almost imperceptible. Even as the table size grows, there'd be no decrease in performance. You could then do a MATCH() into column:
=INDIRECT( "table1[" & C4 & " Type]")
and use that result to do an INDEX() into column
=INDIRECT( "table1[" & C4 & " Qty]")
