# Can I make the row lookup array/range part in an array formula variable?

1. ## Can I make the row lookup array/range part in an array formula variable?

Is it possible to make the row lookup array/range part of this formula variable so that it is first defined by finding a match in a headings column?

=IFERROR(INDEX(BOXES!\$A\$6:\$BY\$26,SMALL(IF(BOXES!\$AL\$6:\$AL\$26>0,ROW(BOXES!\$AL\$6:\$AL\$26)-ROW(BOXES!\$AL\$6)+1),ROWS(E51:E\$51)),MATCH(E\$5,BOXES!\$A\$5:\$BY\$5,0)),0)

I.e. The BOXES!\$AL\$6:\$AL\$26 and BOXES!\$AL\$6 bits would become for example BOXES!\$AM\$6:\$AM\$26 and BOXES!\$AM\$6 if a match was first found in AM5 (in range AL5 to BY5).

Can't see anything online like this so all help will be great!

Thanks.

2. ## Re: Can I make the row lookup array/range part in an array formula variable?

Can you post a SMALL sample file that shows us what results you expect?

A SMALL file will have about 20 rows an about 10 columns worth of data.

We should not have to scroll to see/find/look at the data.

3. ## Re: Can I make the row lookup array/range part in an array formula variable?

Try this..

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

The purpose of ROW(BOXES!\$AL\$6:\$AL\$26)-ROW(BOXES!\$AL\$5) is to provide you the row no..
It has nothing to do with the result....

Provide a sample worksheet ..

Don't forget to click *

4. ## Re: Can I make the row lookup array/range part in an array formula variable?

Hi guys,

please see attached an example worksheet which just shows the bare essentials as much as I can reduce them down to.

Sheet 'BOXES' is the source data. Sheet 'CRATING' is where the data needs to end up. The CRATING sheet is currently using the formula I have and does show how the data should be entered however I am having to manually adjust the row lookup ranges for each new 'Crate Consolidation' section in order to use the right column on the BOXES sheet for the row lookup array.

On my actual sheet I am going to have 40 'Crate Consolidations' which is why I want to find a way to select the appropriate column to use as the row look up array based on match in the headings range BOXES!M2:P2.

Happy to elaborate if need be and thanks.

Thanks.

[ATTACH]333175[/ATTACH

5. ## Re: Can I make the row lookup array/range part in an array formula variable?

You currently have this...

Data Range
 A B 5 Consolidation Crate 1 ------ 6 2 7 1 8 0 9 0 10 0 11 12 13 Consolidation Crate 2 14 2 15 2 16 0 17 0 18 0

If you did this...

Data Range
 A B 5 ------ 6 Consolidation Crate 1 2 7 Consolidation Crate 1 1 8 Consolidation Crate 1 0 9 Consolidation Crate 1 0 10 Consolidation Crate 1 0 11 12 13 14 Consolidation Crate 2 2 15 Consolidation Crate 2 2 16 Consolidation Crate 2 0 17 Consolidation Crate 2 0 18 Consolidation Crate 2 0

Then you could use an INDEX/MATCH to get the range you need.

Are you open to that idea?

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1