View Single Post
  #15   Report Post  
Harlan Grove
 
Posts: n/a
Default

Alan Beban wrote...
....
=INDIRECT(ADDRESS(
ROW(DataTable)+INT((ROW()-ROW(CxRV))/COLUMNS(DataTable)),
COLUMN(DataTable)+MOD(ROW()-ROW(CxRV),COLUMNS(DataTable)),4,,"Sheet8"))

....
=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.

....

Ugh! Not the best way by a long shot! All it takes is a single array
formula

=INDEX(DataTable,MATCH(TRUE,COUNTIF(OFFSET(DataTab le,
ROW(DataTable)-CELL("Row",DataTable),0,1,),J1)0,0),COLUMNS(DataT able))