Hi Domenic,

Thank you very much for providing a solution.

I thought it may complicate things by returning both the criterion and
numeric value to the same cell.

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.

Yes, it woud be great to have the criterion and numeric value returned in the
same cell.

Cheers,
Sam

Domenic wrote:
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!

Hi Domenic,

[quoted text clipped - 48 lines]
Thanks
Sam

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...tions/200604/1