Assumptions:

Sheet2 will contain the results

Column B, starting at B2, will contain the criteria

Each row will index a different column from the dynamic range named
'Data'. For example, Row 2 will index Column E, Row 3 will index Column
F, etc.

Defined Name:

Select Sheet2!C2

Insert Name Define

Name: DataIdx

Refers to:

=INDEX(Data,0,ROWS(Sheet2!C$2:C2))

Click Ok

Formulas:

C2, copied down:

=SUMPRODUCT(--(DataIdx=B2),--(ROW(DataIdx)-MIN(ROW(DataIdx))+11))

D2, copied across and down:

=IF(COLUMNS($D2:D2)<=$C2*2,IF(MOD(COLUMN()-COLUMN($D2),2)=0,$B2,INDEX(Dat
aIdx,LARGE(IF(DataIdx=$B2,ROW(DataIdx)-MIN(ROW(DataIdx))+1),INT((COLUMN()
-COLUMN($D2))/2)+1)-1)),"")

....confirmed with CONTROL+SHIFT+ENTER.

Note:

Here is one occasion where it would be easier to have the criterion and
numeric value returned in the same cell instead of separately. Post
back if you'd prefer it this way.

Hope this helps!

In article <5f6075872f735@uwe, "Sam via OfficeKB.com" <u4102@uwe
wrote:

Hi Domenic,

Apologies for not responding sooner and my incorrect Sample Layout.

Some clarification...
With the data laid out as you've described, do you mean the 'numeric value
that's located in the row below'?

Yes, the Row below

Or should the row numbers for your data be in reverse order?

Sorry, No.

I should have said the search for the Numeric Criterion will start from the
bottom of the Dynamic Range up - so, Row1000 is the bottom /start and Row1 is
the top /end. If there is a Numeric Criterion at the top (Row1) of the
Dynamic Range (and no cell above) still Return the Numeric Criterion but
with "Empty Text" for the missing Numeric Value.

Correction: -
Sample Data & Layout:
RowNo. Col"E" Col"F" etc
Row37 30 04
Row38 64 64
Row39 27 04
Row40 05 05
Row41 20 20
Row42 80 72
Row43 88 04
Row44 05 08
Row45 50 27
Row46 42 39
Row47 30 04
Row48 05 04
Row49 05 50
Row50 59 19

Cheers,
Sam

Domenic wrote:
Some clarification...

With the data laid out as you've described, do you mean the 'numeric
value that's located in the row below'? Or should the row numbers for
your data be in reverse order?

Hi All,

[quoted text clipped - 54 lines]
Thanks
Sam