Prev: Lookup function question
Next: Protected Sheets
From: Glenn on 30 Mar 2010 13:07
Gary''s Student wrote:
> Without VBA. Say B2 contains your value (40237)
> In V2 enter the following array formula:
>
> =ADDRESS(MAX((A1:U200=40237)*ROW(A1:U200)),MAX((A1:U200=40237)*COLUMN(A1:U200)),4)
>
> This must be entered with CNTRL-SHFT-ENTER rather than just the ENTER key.
>
> The formula will display "B2". To get the contents of the cell BELOW B2:
> =OFFSET(INDIRECT(V3),1,0)
>
> You may be able to combine these into a single formula


Nice. Just add one to the row and wrap with INDIRECT (still an array formula):

=INDIRECT(ADDRESS(MAX((A1:U200=40237)*ROW(A1:U200))+1,
MAX((A1:U200=40237)*COLUMN(A1:U200)),4))
From: T. Valko on 30 Mar 2010 13:12
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" <nelly(a)discussions.microsoft.com> wrote in message
news:C29B06BF-9A91-4653-AC2E-3112021DD2F7(a)microsoft.com...
> 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


First  |  Prev  | 
Pages: 1 2
Prev: Lookup function question
Next: Protected Sheets