Hi Guys,
I am trying to use an array formula to give me all instances of matching values but where the row lookup array is dynamic and first determined by where a match is found in the headings row.
I have an array formula working well at the moment where the row lookup array stays the same each time but I am now trying to adapt it to be dynamic and this is not working for me.
The attached Worksheet should hopefully explain more clearly. The red text on the CRATING sheet shows how the data would look given the current data set in the BOXES sheet.
Basically the formula in D53 on the CRATING sheet should find a match for C52 from CRATING sheet (in this example that's "Consolidation Crate 4") in the array (BOXES!AL5:BY5) and where it finds a match this will give the row lookup array - in this instance that would be (BOXES!AO6:AO26). Next the lookup value is ANY number greater than 0 (rather than a specific value). This should finally then give me the row number that I can use with the rest of the formula.
The basic formula i have at the moment is and the underlined bits are what I need to make dynamic or change the lookup value.:
=IFERROR(INDEX(BOXES!$A$6:$BY$26,SMALL(IF(BOXES!$AJ$6:$AJ$26=CRATING!$C$52,ROW(BOXES!$AJ$6:$AJ$26)-ROW(BOXES!$AJ$6)+1),ROWS(D$53:D53)),MATCH(D$7,BOXES!$A$5:$BY$5,0)),"")
(Note: D$7 will actually be C52 in order to get the qty for that crate)
Any help you be great. This is probably a bit complex given my current knowledge of array formulas but eager to learn!
Please see attached worksheet and I'll be happy to elaborate if need be.
Thanks as ever.
Dynamic Row Lookup Array within Array formula.xls
Bookmarks