Dynamic Row Lookup Array within Array formula

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

Hi Jacc,

That's amazing! Works perfectly for the quantity. Than you.

I'd be grateful if you could explain briefly how it works

I'm also looking at it trying to see how it could be adapted to return the other pieces of relevant information but not sure on this.


Thanks very much again.

No worries.

I'll be continuing to work on it where I'm able in the mean time.

Look forward to hearing back when you get a mo.

No worries.

I'll be continuing to work on it where I'm able in the mean time.

Look forward to hearing back when you get a mo.

Hi Jacc,


Have you had any thoughts on this? I have posted a separate question as appreciate your possibly busy at the mo.

Thanks anyway.