Harlan Grove wrote:
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))

Well, isn't that interesting! I would have thought the suggestion would
be something like fill down a range on Sheet8, for example, named CxRV with

=INDIRECT(ADDRESS(
ROW(DataTable)+INT((ROW()-ROW(CxRV))/COLUMNS(DataTable)),COLUMN(DataTable)+MOD(ROW()-ROW(CxRV),COLUMNS(DataTable)),4,,"Sheet8"))
(compliments of Chip Pearson)

and then enter something like

=INDEX(DataTable,INT((MATCH(J19,CxRV,0)-1)/COLUMNS(DataTable))+1,COLUMNS(DataTable))

in order to use only built-in functions and avoid at all costs the
dreaded Array Functions.

But no, here's Harlan Grove, instead carping about which of the dreaded
Array Functions is more efficient. Onward and upward!

Alan Beban