# Dynamic Row Lookup Array within Array formula

1. ## 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  Register To Reply

2. ## Re: Dynamic Row Lookup Array within Array formula

Try this in D53:
Formula:  `Please Login or Register  to view this content.`

Note the 0 in the INDEX function which is used to return the entire column.
Array formula of course, Ctrl + Shift + Enter.  Register To Reply

3. ## Re: Dynamic Row Lookup Array within Array formula

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.  Register To Reply

4. ## Re: Dynamic Row Lookup Array within Array formula

Glad to hear it works! I'd be happy to continue in a day or two, working 12 hour days right now.   Register To Reply

5. ## Re: Dynamic Row Lookup Array within Array formula

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.   Register To Reply

6. ## Re: Dynamic Row Lookup Array within Array formula

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.   Register To Reply

7. ## Re: Dynamic Row Lookup Array within Array formula

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.  Register To Reply