Index array with dynamic range using Match Function

[jozzy]

Hi all,

As with the screenshots below I am struggling (in my head) to figure out a way to turn my 'Product!$AK$31:$IQ$31>0' & 'Product!$AK$29:$IQ$29' into a dynamic range (PIC1) i.e. I want to be able to insert a Match formula to reference cell AE57 - 'Panel_1' & "Product Description" which are both in a named range on the 'Product' Worksheet (PIC3).

The same applies for when I want to list the quantities in an Array (PIC2). I have been able to Match 'Panel_1' Cell AE57 to return the row number however as seen the range is absolute. Again I would like for this to be dynamic when looking up the 'Product' worksheet table.

(PIC1)

(PIC2)

(PIC3)

Sample Document:
https://onedrive.live.com/redir?resid=79626E1654A1048F%21662736


Your help is greatly appreciated. Please post should you have further questions or require more information to understand what the requirements are.

Many Thanks,

Sean

Excel 2010/2016

 

[jozzy]

Just to reiterate the formula I currently have in place (absolute formula) is as follows:
{=IFERROR(INDEX('Vide - Product'!$A$29:$IQ$29,1,SMALL(IF('Vide - Product'!$BA$31:$IQ$31>0,COLUMN('Vide - Product'!$BA$29:$IQ$29)),ROW()-56)),"")}

This returns the result in the description column in cell AG57: "Vertical Closed Extrusion Snap-in"

The following formula:
{=IFERROR(INDEX('Vide - Product'!$A$29:$IQ$95,MATCH($AE57,VIDE_PRODUCT_TABLE_PANEL_NO,0),SMALL(IF('Vide - Product'!$BA$31:$IQ$31>0,COLUMN('Vide - Product'!$BA$29:$IQ$29)),ROW()-56)),"")}

Returns the result in Quantity Column cell AM57: "2"

Both results are correct however, I require the ranges (highlighted in red above) to be 'dynamic' (I believe is the correct term) to change row depending on the value in cell AE57: 'Panel_1'

Sample Document:
https://onedrive.live.com/redir?resid=79626E1654A1048F!662736

Again if I can assist in anyway in making this more clearer please let me know.


Cheers,

Sean