As long as there is only a single instance of the date...

Array entered** :

=INDEX(A1:U200,MAX(IF(A1:U200=40237,ROW(A1:U200)))-1,MAX(IF(A1:U200=40237,COLUMN(A1:U200))))

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT
key then hit ENTER.

--
Biff
Microsoft Excel MVP


"nelly" wrote in message
...
HI does anyone know of a workaround as a formula for this VBA code.

Range("A1:U200").Select
test = Selection.Find(What:="40237", After:=ActiveCell,
LookIn:=xlValues, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Address

I thought it easier to explain this way.

40237 is a date and can be anywhere in the above range. I need to get the
value from the cell below.

Thanks in advance

Nelly