Alan Beban wrote...
Bob Phillips wrote:
Just for interest, two less functions
=INDEX($G$1:$G$6,(MID(J19,2,9)-1)/7+1)
For a formula that is independent of the size of the table of data or
the regularity of its contents, if the functions in the freely
downloadable file at http://home.pacbell.net are available to your workbook
=OFFSET(INDIRECT(ArrayMatch(J19,dataTable,"A")),0 ,
-INDEX(ArrayMatch(J19,dataTable),1,2)+COLUMNS(dataT able))
Assuming someone would use your function library, wouldn't they want to
do so efficiently? Only one udf call needed (MakeArray).
=INDEX(DataTable,INT((MATCH(J19,MakeArray(DataTabl e,1),0)-1)
/COLUMNS(DataTable))+1,COLUMNS(DataTable))