Additional Columns to be Searched

I was just given the formala below. It works great. I need to incorporate
three additional scenarios.

=INDEX(D:D,MATCH(MAX(C:C),C:C,0))

I have three additional scenarios:
1. Can you do the same thing but if there is no values have it left blank or
return Open? (Currently, it shows #NA)

2. Can you do the same thing except just include odd numbered rows? (ie. D3,
D5.... D589)?

3. Can you do the same thing except instead of the biggest date have the
second biggest date?

Thanks,

Aviator.

In article <[email protected]>,
"Aviator" <[email protected]> wrote:

> I was just given the formala below. It works great. I need to incorporate
> three additional scenarios.
>
> =INDEX(D:D,MATCH(MAX(C:C),C:C,0))
>
> 1. Can you do the same thing but if there is no values have it left blank or
> return Open? (Currently, it shows #NA)

=IF(COUNT(C:C),INDEX(D:D,MATCH(MAX(C:C),C:C,0)),"Open")

> 2. Can you do the same thing except just include odd numbered rows? (ie. D3,
> D5.... D589)?

A3:

For every second row starting with the first cell in the range...

=MAX(IF(MOD(ROW(C3:C589)-CELL("row",C3)+0,2)=0,C3:C589))

....confirmed with CONTROL+SHIFT+ENTER.

For every second row starting with the second cell in the range...

=MAX(IF(MOD(ROW(C3:C589)-CELL("row",C3)+1,2)=0,C3:C589))

....confirmed with CONTROL+SHIFT+ENTER.

A4:

=IF(COUNT(C3:C589),INDEX(D3:D589,MATCH(A3,C3:C589,0)),"Open")

> 3. Can you do the same thing except instead of the biggest date have the
> second biggest date?

A3:

For every second row starting with the first cell in the range...

=LARGE(IF(MOD(ROW(C3:C589)-CELL("row",C3)+0,2)=0,C3:C589),2)

....confirmed with CONTROL+SHIFT+ENTER.

For every second row starting with the second cell in the range...

=LARGE(IF(MOD(ROW(C3:C589)-CELL("row",C3)+1,2)=0,C3:C589),2)

....confirmed with CONTROL+SHIFT+ENTER.

A4:

=IF(COUNT(C3:C589),INDEX(D3:D589,MATCH(A3,C3:C589,0)),"Open")

Hope this helps!

Domenic,

Thank you for your help. The first question you answered worked out great. I
am still having problems with the other two. Have have played with multiple
combonations of formulas which you sent. Examples below:

=IF(COUNT(E5:E555),INDEX(B5:B555,MATCH(LARGE(IF(MOD(ROW(E5:E555)-CELL("row",E5)+0,2)=0,E5:E555),2),E5:E555,0)),"Open")

This gives me a value but it's not doing every second row.


=IF(COUNT(E5:E555),INDEX(B5:B555,MATCH(LARGE(IF(MOD(ROW(E5:E555)-CELL("row",E5)+1,2)=0,E5:E555),2),E5:E555,0)),"Open")

This returns "#NUM!"

Could I be missing an add-on or something? What do you mean with
".... confirmed with CONTROL+SHIFT+ENTER."
Maybe I am doing something wrong. Can you help.

Thank you,

Aviator



"Domenic" wrote:

> In article <[email protected]>,
> "Aviator" <[email protected]> wrote:
>
> > I was just given the formala below. It works great. I need to incorporate
> > three additional scenarios.
> >
> > =INDEX(D:D,MATCH(MAX(C:C),C:C,0))
> >
> > 1. Can you do the same thing but if there is no values have it left blank or
> > return Open? (Currently, it shows #NA)
>
> =IF(COUNT(C:C),INDEX(D:D,MATCH(MAX(C:C),C:C,0)),"Open")
>
> > 2. Can you do the same thing except just include odd numbered rows? (ie. D3,
> > D5.... D589)?
>
> A3:
>
> For every second row starting with the first cell in the range...
>
> =MAX(IF(MOD(ROW(C3:C589)-CELL("row",C3)+0,2)=0,C3:C589))
>
> ....confirmed with CONTROL+SHIFT+ENTER.
>
> For every second row starting with the second cell in the range...
>
> =MAX(IF(MOD(ROW(C3:C589)-CELL("row",C3)+1,2)=0,C3:C589))
>
> ....confirmed with CONTROL+SHIFT+ENTER.
>
> A4:
>
> =IF(COUNT(C3:C589),INDEX(D3:D589,MATCH(A3,C3:C589,0)),"Open")
>
> > 3. Can you do the same thing except instead of the biggest date have the
> > second biggest date?
>
> A3:
>
> For every second row starting with the first cell in the range...
>
> =LARGE(IF(MOD(ROW(C3:C589)-CELL("row",C3)+0,2)=0,C3:C589),2)
>
> ....confirmed with CONTROL+SHIFT+ENTER.
>
> For every second row starting with the second cell in the range...
>
> =LARGE(IF(MOD(ROW(C3:C589)-CELL("row",C3)+1,2)=0,C3:C589),2)
>
> ....confirmed with CONTROL+SHIFT+ENTER.
>
> A4:
>
> =IF(COUNT(C3:C589),INDEX(D3:D589,MATCH(A3,C3:C589,0)),"Open")
>
> Hope this helps!
>

Instead of entering the formula with just ENTER, press the CONTROL and
SHIFT keys, then while you have those two keys pressed down hit the
ENTER key. Excel will then place braces {} around the formula, which
will tell you that you've entered the formula correctly.

Hope this helps!

In article <[email protected]>,
"Aviator" <[email protected]> wrote:

> Domenic,
>
> Thank you for your help. The first question you answered worked out great. I
> am still having problems with the other two. Have have played with multiple
> combonations of formulas which you sent. Examples below:
>
> =IF(COUNT(E5:E555),INDEX(B5:B555,MATCH(LARGE(IF(MOD(ROW(E5:E555)-CELL("row",E5
> )+0,2)=0,E5:E555),2),E5:E555,0)),"Open")
>
> This gives me a value but it's not doing every second row.
>
>
> =IF(COUNT(E5:E555),INDEX(B5:B555,MATCH(LARGE(IF(MOD(ROW(E5:E555)-CELL("row",E5
> )+1,2)=0,E5:E555),2),E5:E555,0)),"Open")
>
> This returns "#NUM!"
>
> Could I be missing an add-on or something? What do you mean with
> ".... confirmed with CONTROL+SHIFT+ENTER."
> Maybe I am doing something wrong. Can you help.
>
> Thank you,
>
> Aviator