how do i referance an adjacent cell from a search?

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

UH-60 Crew Chief - 17 May 2007 20:29 GMT

i have a table in excell, Column A is the Date of the flight, Column B is the
mode (day, night, NVG) and column C is the number of hours flown.

i need the formula to find the most recent date that i flew night in one
cell and in another cell i need to find the most recent date that i flew NVG.

can anyone help?

Reply to this Message

excelent - 17 May 2007 21:55 GMT

=INDEX(A1:A30,MATCH(MAX(IF($B$1:$B$30="NVG",$A$1:$A$30,0)),$A$1:$A$30,0))

insert and hit ctrl+shift+enter (array)

"UH-60 Crew Chief" skrev:

> i have a table in excell, Column A is the Date of the flight, Column B is the
> mode (day, night, NVG) and column C is the number of hours flown.
[quoted text clipped - 3 lines]
>
> can anyone help?

Reply to this Message

UH-60 Crew Chief - 18 May 2007 14:54 GMT

I tried this and in the formula arguement it works perfectly but in the
spread sheet it displays #value.

> =INDEX(A1:A30,MATCH(MAX(IF($B$1:$B$30="NVG",$A$1:$A$30,0)),$A$1:$A$30,0))
>
[quoted text clipped - 9 lines]
> >
> > can anyone help?

Reply to this Message

UH-60 Crew Chief - 18 May 2007 14:57 GMT

Worked great in formula arguement box, but when i hit ctr-shift-enter it
gives the error "Array formulas are not valid in merged cells"

> =INDEX(A1:A30,MATCH(MAX(IF($B$1:$B$30="NVG",$A$1:$A$30,0)),$A$1:$A$30,0))
>
[quoted text clipped - 9 lines]
> >
> > can anyone help?

Reply to this Message

Gord Dibben - 18 May 2007 20:35 GMT

Get rid of the merged cells.

One more reason not to use merged cells............one I never ran across
before.

I'll add it to the long list of problems caused by these.

Gord Dibben MS Excel MVP

>Worked great in formula arguement box, but when i hit ctr-shift-enter it
>gives the error "Array formulas are not valid in merged cells"
[quoted text clipped - 12 lines]
>> >
>> > can anyone help?

Reply to this Message